Incorrect Sum on Doubles

[chris-uk-lad]

I have a table of numeric values (Double, 6dp, Fixed), when I try to sum these together in a query I get incorrect results (e.g. 100.8 – 100.7 = 9.99999999999943E-02 instead of .01.

I’ve tried experimenting with the data type, making them standard instead of fixed, using CDbl/CLng, aswell as my own function which does the following to convert to use CDec as this is in Access 97:

Function Subtract_val(val1 As Double) As Double 
Subtract_val = CDec(val1)
End Function

Might anyone have an idea of how to sum these correctly?

Much appreciated

 

[vbaInet]

Try this:

round(CDbl(100.8) - CDbl(100.7), 2)

 

[chris-uk-lad]

Im don't believe round will be enough for the larger values with 6 dcp and i have to sum together 50+ values (as i'm trying to find those that equal zero eaxactly after the sum).

 

[vbaInet]

What is wrong with Round?? Isn't that a matter of simply changing the level of precision from 2 to 6?

If you are summing 50+ values on one record, you will still need to type it out one by one.