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
JeffreyDurhamAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
I replicated your result with a fairly straight forward function
Function TheFix(first As Double, second As Double) As Double
TheFix = Fix(first * second)
End Function


But while Doubles can hold a LOT of precision, they are NOT decimals -- and math with them can yield results different from what we expected from our scientific calculators.  You'll see this sometimes in operations where you multiply a number by a fraction and divide it by a fraction and wind up with an infinitesimally different result.  Especially in currency applications.

if you coerce your Doubles to Decimals, you get the expected results.
Function TheFix(first As Variant, second As Variant) As Double
TheFix = Fix(CDec(first) * CDec(second))
End Function


It's not so much a bug as it is a limitation of how computers do math.
Someone may supply much more technical details -- but in general, if you want 'scientific calculator' type expected results, work with Decimals.

Doubles are 8-byte floating point numbers
Decimals are 12-byte signed integers scaled by a power of 10 and are precise to 28 digits.
Sometimes that difference matters!
https://support.microsoft.com/en-us/kb/78113

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hamed NasrRetired IT ProfessionalCommented:
This is why we have data types. Declare the variable as Currency.
Computers use memory locations to store data.

? 251.48 * 100.0
 25148
? 25148 - 251.48 * 100.0
 1.02318153949454E-12

If you declare data as currency, that solves the problem.
   
 Dim c As Currency
    c = 251.48
    Debug.Print Fix(c * 100#)

Open in new window

==> 25148
Gustav BrockCIOCommented:
As already explained: No bug, only a floating point error.

You can use the generic functions here:

Rounding values up, down, by 4/5, or to significant figures

The Test module list a lot of other "strange" results.

/gustav
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Hamed NasrRetired IT ProfessionalCommented:
or simply:
Fix(CCur(251.48)*100.0)
Gustav BrockCIOCommented:
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
PatHartmanCommented:
This is an excellent article on the subject written by my friend Luke Chung, president of FMSINC

http://www.fmsinc.com/TPapers/math/index.html

This is a great site to add to your bookmarks.  Lots of informative articles in addition to samples and software tools.
Hamed NasrRetired IT ProfessionalCommented:
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.
Nick67Commented:
:)
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.
JeffreyDurhamAuthor Commented:
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
Nick67Commented:
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.
Hamed NasrRetired IT ProfessionalCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.