invalid compare results in a query

[Betsy]

I am comparing many records from two different tables. On
99.9% of the records, the compare results are accurate.
But on two or three of the records, even though the data I
am comparing from the two tables are equal, the query
shows they are not equal. The two fields are 1) long
integer and 2) double. I have tried looking in excel
(from which the tables were imported) to see if there is
significant data past the first two positions to the right
of the decimal, but there are not.

Any suggestions?

 

[Dale Fye]

Double precision comparisons are imprecise. Usually, if you want to
compare double precision numbers, your best bet is do do something
like

ABS(A.dblField - B.dblField) < .000005

Where .000005 can be any degree of accuracy you are interested in.

--
HTH

Dale Fye


I am comparing many records from two different tables. On
99.9% of the records, the compare results are accurate.
But on two or three of the records, even though the data I
am comparing from the two tables are equal, the query
shows they are not equal. The two fields are 1) long
integer and 2) double. I have tried looking in excel
(from which the tables were imported) to see if there is
significant data past the first two positions to the right
of the decimal, but there are not.

Any suggestions?

 

[martin]

This happened to me a few times. Use the round function on
the long and double fields and then it will work. It has
insignicant digits which make it not equal 0.

Martin