Using <> Not Equals Criteria

[Oscar]

I wish to find data which does not equal (eg From_Depth in two different tables), and am using the criteria From_Depth from table 1 <> From_Depth from table2. This is working partly as is giving me the data which is True for this criteria, however I am also getting some data which do have equal From_Depths (as far as I can see). I have made sure that both columns in the tables have the same FieldSize (Double) and both have Auto Decimal Places. Any suggestions?

 

[neileg]

How equal is equal?

Sometimes when using figures that are derived from different calculations, they have different small decimal parts. Eg 1.002005 and 1.0020049. This may be displayed as 1.002005 in each case, but there is a difference.

To avoid this, you could round the results, or alternatively test for a difference less that the precision you need. Eg Table1.From_Depth - Table2.From_Depth <0.0000001

Remember to account for negative and positive differences.

 

[Pat Hartman]

Unless you really need the precision, Single and Double are a lot of trouble to work with once you done any math on the field due to the rounding errors that always result. Strange as it may seem, the currency format is a better choice because it does not develop these stupid! errors.

If you want to obtain an understanding of why the errors occur, read the excellent article regarding the problem at www.fmsinc.com. Look for the paper entitled, "When Access Math doesn't add up".

http://www.fmsinc.com./tpapers/math/index.html