Query Calculation gives wrong answer

[Gio Bacareza]

I have 2 fields in a table. Field A Data Type is Currency. Field B Data Type
is Number, Field Size=Single, Format=Standard, Decimal Places=3.

When I do a query and add a calculated field, Field C = Field A x Field B,
the result is inaccurate. For Example, when Field A=2000 and Field B=0.900,
the result of the query shows Field C = 1799.99995231628.

Furthermore, when I export the query to excel, it shows Field B to be
0.899999976158142. Field A remains as 2000.

Why does Access convert the value of Field B, which was EXPLICITLY ENTERED
as 0.9 to 0.899999976158142. This is what I think is the reason the
resulting calculation gives out 1799.99995231628 instead of the exact 1800?

Is this an Access bug or what am I doing wrong?

Please help.

Thanks

 

[Allen Browne]

This is not an issue with Access, but with computers in general.

Floating point numbers (such as Single and Double) cannot be perfectly
represented in the computer. They are always an approximation. The Single
gives you about the same precision as a hand-held calculator (c. 8 digits).
The Double gives around 17 digits of precision, but still cannot represent
the number exactly. Whenever you work with floating point numbers on a
computer, you will have rounding errors.

You can easily demonstrate that the computer is unable to store 0.9
precisely as a single: Try converting it to a double. In the Immediate
Window (Ctrl+G), enter:
? CDbl(0.9!)
The answer is:
0.899999976158142
Note that the answer is correct when rounded to 7 or 8 places, and after the
precision of the Single was inadequte.

If you need to store precisely 4 decimal places or fewer, you can use a
Fixed Point data type instead of a Floating Point type. Currency is a fixed
point that always has 4 decimal places of precision. You can think of this
data type as being an Integer type: the computer removes the decimal point,
does integer math, and then pops the decimal point back into the answer. As
a result, there is no rounding error.

Again, in the Immediate window, try:
? 0.9@ * 2000@
and you see precisely 1800. That's very different to:
? 0.9! * 2000@
which yields 1799.99995231628

In Access 2000 and later there is also a Decimal data type which is scalar.
However, I advise you NOT to use this type unless you are really desperate:
it is buggy (e.g. http://allenbrowne.com/bug-08.html ) and incompletely
implemented (e.g. VBA does not have a Decimal type, so you are limited to a
Variant of subtype Decimal).

(Note: the "!" at the end of a literal number signifies a Single type, and
the "@" signifies a Currency type.)

 

[John Vinson]

Why does Access convert the value of Field B, which was EXPLICITLY ENTERED
as 0.9 to 0.899999976158142.

Because Single is a floating point datatype - which is an
*approximation*. Just as you cannot represent 1/7 exactly as a decimal
fraction, 0.9 cannot be represented exactly as a binary fraction
(which is the basis of a Float number).

Use a Currency datatype if four decimal places will suffice. Currency
is a scaled huge integer with exactly four decimals, and none of the
roundoff error problems you get with Float or Double.