Formula or Numeric Formatting Issue

[Jeff]

I have two identically structured tables. We do the same data entry in both
tables and compare the results to identify data entry errors. While
comparing two numeric fields (Numberic, Double, decimal place set to auto) ,
I encountered errors that are not actual errors. I'm presuming its a
formula error. My query identifies all records where tableONE.fieldOne <>
tableTWO.fldOne. However when viewing these records they both say 0.57 and
0.57 ... if I subtract the two, the difference shows as 0, however when I
click on the difference it shows as -1.11022302462516E-16. What is going
on? Do I need to change the format.

Thanks in advance.

 

[Marshall Barton]

I have two identically structured tables. We do the same data entry in both
tables and compare the results to identify data entry errors. While
comparing two numeric fields (Numberic, Double, decimal place set to auto) ,
I encountered errors that are not actual errors. I'm presuming its a
formula error. My query identifies all records where tableONE.fieldOne <>
tableTWO.fldOne. However when viewing these records they both say 0.57 and
0.57 ... if I subtract the two, the difference shows as 0, however when I
click on the difference it shows as -1.11022302462516E-16. What is going
on? Do I need to change the format.

This is a typical issue with floating point numbers. THe
usual solution is to compare the difference to a very small
number and accept them as equal if the difference is less
than the margin of error.

Instead of using
tableONE.fieldOne <> tableTWO.fldOne
use
Abs(tableONE.fieldOne - tableTWO.fldOne) > 1E-6
or whatever degree of precision you require.