I am using MSAccess 2010 on Windows 7 64.
I am comparing two tables in a query. I am joining on a composite PK and selecting rows where table A column A <> table B column A where column A for both tables is a double.
For 120 rows it works. For 28 rows a value of 0.088 in table A column A <> 0.088 in table B column A.
There are no text functions used ANYWHERE.
When I add a column to the query a:[TableA]![ColumnA] - [TableB]![ColumnA] I return values like -1.38777878078145E-17.
When I export the query to Excel and do the math the result = 0.
How is it possible that 0.088 - 0.088 <> 0?
How is it possible that 0.088 double <> 0.088 double?
This is a very common problem related to the nature of floating-point numbers in any computing context (not just Access). It seems like a paradox, but the fact is that a Double value can approximately represent a huge range of numbers very precisely (up to 15 significant digits, ref: here), but that representation is almost never exact. That's why:
exact comparisons of floating numbers (e.g. x=y) can sometimes fail, and therefore
you should never rely on a JOIN between two Double fields.
More information on floating-point numbers is available here. Wikipedia also addresses the issue here.
If you have fields with decimal places that require exact comparisons then you may want to consider changing them to Currency or Decimal.
