Link to home
Start Free TrialLog in
Avatar of Jeanette Durham
Jeanette DurhamFlag for United States of America

asked on

vba access, Why does fix((251.48*100.0)) return 25147

I think I might've found a bug in access but I'm not sure.. I could be misunderstanding. So the question really boils down to, is this a bug in access, or is there something about the fix function I'm not understanding that would cause this result..
I'm using vba in access 2013.

AFAIK, fix should return the integer portion of a decimal.
(https://support.office.com/en-nz/article/Int-Fix-Functions-5446da0c-fbdc-425f-a818-7e3153984ff8)

So, the main line of code in my program that is causing off results boils down to..
fixDigits = CDbl(Fix(dNumber * dMultiplier)) / dMultiplier

so the issue seems to occur when you call fix with an expression inside it - in this case dNumber*dMultiplier, but not any other way.. here's some evals from the immediate window:


?fix(251.48)
 251
?fix(251.48*100) / 100
 251.47
?fix(251.48*100)
 25147
?fix((251.48*100.0))
 25147
?fix(25148)
 25148

what do you guys think? Bug in access or intended behaviour (and if so, why?)

Thanks!
~Jeffrey



-------------------------------------------------------------------------------------------------------

I was able to fix it by altering my fixDigits function such that it calculated dResult first and then took fix(dResult)..
Hence:

Public Function fixDigits(ByVal dNumber As Double, Optional ByVal iNumDecimalPlaces As Integer = 2) As Double
    'This will take a decimal and without rounding it, return the same number but only so many digits after the decimal
    '?fixDigits(352.222333) => 352.22  'default is 2 digits after the decimal
    Dim dMultiplier As Double, dResult As Double
    On Error GoTo StringMethod
    dMultiplier = 10 ^ iNumDecimalPlaces
    'Mind-blowing, access error? fix(251.48 * 100) => 25147
    'To fix, only fix the result of dNumber * dMultiplier, but calc result first
    dResult = dNumber * dMultiplier
    fixDigits = CDbl(Fix(dResult)) / dMultiplier
    Exit Function
StringMethod:
    'The multiplier or the number * the multiplier probably caused an overflow, just treat it like a string instead
    fixDigits = CDbl(FormatNumber(dNumber, iNumDecimalPlaces))
End Function

?fixdigits(251.48, 2)
 251.48
ASKER CERTIFIED SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
or simply:
Fix(CCur(251.48)*100.0)
True. In general - as this question demonstrates - never never use anything else than data type Currency when you deal with amounts and accounting.

If using Double (not to say Single) you will be hosed sooner or later.

/gustav
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Actually,
?2.8 - 2.7
 9.99999999999996E-02

Reason:
2.8 is stored in 4 bytes (32 bits) as
(4) (7) which is  0.7 * 4
.7 requires more than 23 bits to be stored correctly.
Try it: .7 * 2 = 1.4 -1 = .4* 2 = .8 -0 = .8 * 2 = 1.6 -1 =.6 *2 = 1.2 -1 = .2 ......
.7 = .10110....

2.7 is stored in 4 bytes (32 bits) as
(4) (675) which is  0.675 * 4
.675 requires more than 23 bits to be stored correctly.

0.5 requires 1 bit.
0.75 requires 2 bits
0.625 requires 3 bits.
0.6251 requires more than 23 bits
hence:
?.6251-.625
 9.9999999999989E-05

Currency type allows for that accuracy.

So avoid headache by using proper type for calculations.
:)
Currency is a Decimal-like data type with four digits to the left of the decimal and four to the right.
And as the conclusion of @PatHartman's link says:

With the simple addition of the CDec conversion, [it]... returns the correct result

The data types that store fractional numbers as binary (single, double, etc) can return arithmetic results that differ slightly from data types that store fractional numbers as integer calculations (decimal, currency etc).  My link discussed the how's and why's of that trade-off.
Computers can store data more efficiently and compute with it more quickly if it is in binary format -- but there are trade-offs in accuracy resulting from the compromise in precision -- as you have re-discovered for yourself!

It is not a bug in Access or a misunderstanding of FIX that you have come across.
What you have discovered is that there are binary number storage formats and integer number storage formats -- and they exist for good reasons and are not all equivalent.
Avatar of Jeanette Durham

ASKER

Wow, well thank you everyone for explaining how that works, what the proper solutions are and what articles to read to explain the nitty gritty details! (Those were a lot of fun to read!) Now I know that I need to use currency or decimals when dealing with these kind of values, especially with money values. I had no idea doubles were so limited. Hope everyone has a great rest of their weekend! ~Jeffrey
My bad
Currency is a Decimal-like data type with four digits to the left of the decimal and four to the right.

That should be

Currency is a Decimal-like data type with fifteen digits to the left of the decimal and four to the right.
Adding to previous comment:

?.6251-.625
 9.9999999999989E-05

Using proper type yields exact result:
?ccur(.6251)-.625
 0.0001
?.6251-ccur(.625)
 0.0001
?ccur(.6251)-ccur(.625)
 0.0001