I have a number that may contain a decimal portion: 1.00, 1.33, 40.25.
I need to separate this number to two different fields: 1 and 0, 1 and
33 and 40 and 25.
Any ideas?

Annette wrote:
The Int() function converts a number to integer by truncating the decimal
portion. So that gives you the first field value/
Subtracting the integer from the original number leaves the decimal.
Multiply by 10 or 100 (depending on the size of the number - use Iif() to
decide what multiplication factor to use) to convert the remainder to a
whole number.
I'm assuming we're not dealing with negative numbers - if so, you need to
use the Abs() function.

On Dec 16, 1:27*pm, Annette <annet... (AT) co (DOT) saint-croix.wi.us> wrote:
This is good for 0, 1, or 2 digits.
You can get the whole numberwith
Clng(var)
and the decimal with
Clng((var - clng(var)) * 100)

Ex:
Var = 1.01
? Clng(var) 'dollar
1
? Clng((var - Clng(var)) * 100) 'cents
1

Var = 1.1
? Clng(var) 'dollar
1
? Clng((var - Clng(var)) * 100) 'cents
10

Patrick Finucane wrote:
:-)
Try 1.54

A VBA example that uses no calculations:

Dim varNumber As Variant
Dim dblNumber as Double
Dim lngNumber1 As Long
Dim lngNumber2 As Long
dblNumber = 40.25
varNumber = Split(CStr(dblNumber), ".")
lngNumber1 = CInt(varNumber(0)) ' Whole number part of number
lngNumber2 = CInt(varNumber(1)) ' Decimal number part of number

In the above, lngNumber1 will contain the value 40 and lngNumber2 will
contain the value 25.

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"Annette" <annettem (AT) co (DOT) saint-croix.wi.us> wrote

On Dec 16, 3:58*pm, "Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote:

Well, the logic seemed OK, using the right conversion erroneous...or
so I thought.

I ran the code below. The only way it worked was using
Dim var As Currency

If I used
Dim var As Variant
or
Dim var As Double
in A2010 it would fail from 1.06 to 1.09

Public Sub Junk()
Dim intI As Integer
Dim intJ As Integer
Dim var As Currency

For intI = 1 To 1
For intJ = 0 To 100
var = intI + (intJ / 100)
Debug.Print var; Int(var); (var - Int(var)) * 100
Next
Next
End Sub

On Dec 16, 5:11*pm, "Ken Snell" <kthsneisll... (AT) ncoomcastt (DOT) renaetl>
wrote:
That's pretty slick. I like it.

You need an additional check for whole numbers. Here's your code in a
subroutine.

Public Sub DollarAndCents(dblNumber As Double)
Dim lngNumber1 As Variant
Dim lngNumber2 As Variant
Dim varNumber As Variant

varNumber = Split(CStr(dblNumber), ".")

lngNumber1 = CInt(varNumber(0)) ' Whole number part of number

'check for whole numbers here. Ex: 1.00
If UBound(varNumber) = 1 Then
lngNumber2 = CInt(varNumber(1)) ' Decimal number part of
number
Else
lngNumber2 = 0 ' Whole number.
End If

Debug.Print dblNumber; lngNumber1; lngNumber2
End Sub

Annette wrote:
I guess I'm curious why everyone wants to keep turning this into a VBA
solution. If this is an operation that needs to be performed in many places,
I can see the point. But if you only need to do this once, the entire
process can be done using builtin functions inline in a queriy's select
clause. It's been a while since I did serious performance testing in Access,
but I know that in SQL Server, using scalar user-defined functions can
seriously impair performance if the query returns more than 100 or so rows.
Furthermore, converting to string and back to number is another performance
drain that should be avoided if possible - VBA string-handling is
notoriously inefficient.

I finally got around to testing this and quickly realized my original advice
about treating the decimal portion differently depending on its size was a
red herring. As long as we're dealing only with positive numbers, this works
fine and is fairly simple to maintain:

SELECT [decnumber] ,
Int([decnumber]) as WholeNumPortion,
CInt(100 * Round([decnumber]- Int([decnumber]), 2)) As DecimalPortion
FROM table1

To make it handle negative numbers as well, we need to resort to Iif and
Abs, which of course, makes the code a bit harder to maintain, but I've seen
worse:
SELECT [decnumber] ,
Iif([decnumber]>=0,
Int([decnumber]),
-1 * Int(Abs([decnumber]))
) as WholeNumPortion,
CInt(100 * Round(Abs([decnumber])- Int(Abs([decnumber])), 2))
As DecimalPortion
FROM table1

The problem is distinguishing these two numbers: .54 and -.54. If you test
the query using a table containing a single double column, you will quickly
see that these two values yield the same result (0 and 54), due to the fact
that I am storing the sign with the whole number. When the whole number
portion is zero, the sign is lost. One answer is to store the sign in a
third column as a multiplication factor (1 or -1). That simplifies the
calculation of the whole number portion:

SELECT [decnumber] ,
Iif([decnumber]>=0,1,-1) as Sign,
Int(Abs([decnumber])) as WholeNumPortion,
CInt(100 * Round(Abs([decnumber])- Int(Abs([decnumber])), 2))
As DecimalPortion
FROM table1

This allows you to rebuild the original number (rounded to 2 decimal places)
if necessary using
Sign * (WholeNumPortion + DecimalPortion/100.0)
If the original number had more than 2 significant decimal places, then this
will not match the original number of course.

If this operation does need to be done in more than one query, then a VBA
function is advisable, since you don't want to be maintaining those inline
operations everywhere they are needed. Code reuse and ease of maintenance do
trump performance ... unless the impact to performace is huge, of course.
Here is the code encapsulated in a function (you will need to add some
error-handling, of course):

Function numsplit(num As Double, part As Integer) As Long
'if part contains 0, return the "sign"; 1, return the whole number;
otherwise return the decimal

Select Case part
Case 0
numsplit = IIf(num >= 0, 1, -1)
Case 1
numsplit = Int(Abs(num))
Case Else
numsplit = 100 * Round(Abs(num) - Int(Abs(num)), 2)
End Select
End Function

To use it in a query:

SELECT [decnumber],

numsplit([decnumber],0) as Sign,
numsplit([decnumber],1) as WholeNumPortion,
numsplit([decnumber],2) As DecimalPortion
FROM table1

I've tested these using A2003. I'm puzzled about Patrick's experience with
A2010 - I don't understand why he had to treat 1.09 differently than 1.49.

One more thing, Annette: you mentioned storing the results of these
calculations. This is almost never advisable. it is much better to create a
query that performs these calculations on the fly. Soring them requires you
to write code to recalculate them when the original values change.
Exceptions to this rule include:
1. Calculating on the fly takes too long
2. There is a requirement to view the history of the values, i.e., you need
to know what the value was on a particular date.

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote

The original poster didn't specify the environment for where the calculation
would be done, so I added a VBA example in addition to what had been posted
already.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote

It's an expression solution, it seems, and VBA is certainly one of the
places where expressions are commonly used.

Looking back, I suspect I'd first have asked if the "number" was actually a
numeric field or variable, or characters representing a number. I suspect
that the latter would properly be coerced, however.

And, unless there are a very great many records involved, the performance
differences are negligible. It's nice to have an efficient solution, but
from a practical view, that shouldn't be the first consideration.

Larry Linson
Microsoft Office Access MVP

>