Jeanette Durham

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

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,

End Function

?fixdigits(251.48, 2)

251.48

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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

/gustav

If using Double (not to say Single) you

*will*be hosed sooner or later./gustav

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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 =

.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.

?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:

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.

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.

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

That should be

*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

?.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

Fix(CCur(251.48)*100.0)