Rounding Errors

Z

Zelgrath

I have a field named ScaCollInt and another named
DistributionFactor. Bother are single number fields. I
have a calculation field in a query that multiply's them.
Somethimes I get the expected result and sometimes there
are added digits to the answer or it is slighty lower than
expected. For example, if ScaCollInt is 56 and
DistributionFactor is 0.4 then I might get

22.4 or 22.3999996185303 or 22.4000000156815

Does anyone know why access does this? What are they ways
to get around it?
 
B

Brian

Zelgrath said:
I have a field named ScaCollInt and another named
DistributionFactor. Bother are single number fields. I
have a calculation field in a query that multiply's them.
Somethimes I get the expected result and sometimes there
are added digits to the answer or it is slighty lower than
expected. For example, if ScaCollInt is 56 and
DistributionFactor is 0.4 then I might get

22.4 or 22.3999996185303 or 22.4000000156815

Does anyone know why access does this? What are they ways
to get around it?
Click to expand...

The "single" datatype is what is known as an approximate datatype, hence the
effects you are seeing. This is an unavoidable consequence of the way that
floating-point data works.

You should apply a rounding function to your calculation to get the result
rounded to a suitable number of decimal places. Note that the Round
function in Access applies bankers' rounding, which may or may not be what
you want. Various alternatives are given here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;196652
 
B

Brian

Zelgrath said:
Does the decimal datatype behave in the same manner?
Click to expand...

No, the decimal datatype is not approximate. However, it takes three times
as much storage space as the single datatype, and it is not yet fully
implemented in Access (at least, not in Access 2002 it isn't).

If you need no more than 4 decimal places, you could consider the Currency
datatype. It's half way between single and decimal in terms of storage
requirements, and it is fully implemented.
 
Top