Totals in queries and unexpected results

[Guest]

I'm getting scientific numbers when performing simple math in a query. The
values are equal and do not result in zero. I have tested for null values
before and during calculations. First using a make-table query and append
queries to get all the values in the correct fields (which are defined as
numbers) and then using the totals function to sum all the values. The two
values are equal and the calculated difference is not, often to 14 decimal
places.

 

[John Vinson]

I'm getting scientific numbers when performing simple math in a query. The
values are equal and do not result in zero. I have tested for null values
before and during calculations. First using a make-table query and append
queries to get all the values in the correct fields (which are defined as
numbers) and then using the totals function to sum all the values. The two
values are equal and the calculated difference is not, often to 14 decimal
places.

Double Float numbers are stored as approximations, accurate to
<surprise!> approximiately 14 decimal places (24 binary bits
accuracy). Just as the fraction 1/7 cannot be represented exactly as a
decimal fraction - it's an infinite repeat - so some numbers, such as
1/10, cannot be represented exactly in any floating point number. The
result is that 0.1 + 0.1 + 0.1 - 0.3 is NOT equal to 0.

The solution is to either use a Currency datatype - which is a huge
scaled integer with exactly four, no more no fewer, decimal places and
NO roundoff error; or test for "almost equal" rather than equal, e.g.

< 1.0E-10

instead of = 0.

John W. Vinson[MVP]