Strange results when subtracting 2 fields

[villan60]

I have a table with telephone call records. One of the fields has the price of the call and another contains my calculated price (to check that the charged price is correct). I have a query which subtracts the one from the other and is supposed to show me those where the diff is <> 0. So that I can see all calls that have been charged incorrectly.

The problem is that it returns a lot of calls where the 2 fields are equal but the diff has some strange amount in it.

I have checked that the fields are single and fixed so there are no hidden decimals.

I am very confused and would appreciate any input.

Thanks
Dax

 

[AWithers]

The fields may be formatted to appear with just two decmal places but the actual values may be slightly different (i have had this problem quite a lot especially when importing data)

If you change your criteria to be >0.01 or <-0.01 do they disappear?

Andy

 

[dalchri]

Are you rounding off the calculated field the same way both times? For example, if you round off the calculated price before it is stored in the database table, but you do not round off the &quot;recalculation&quot; in your query, you will get hidden decimals. The decimals are not hidden in the table, but rather in the &quot;recalculation&quot; that you are using to calculate the difference.

For the sake of getting results, I would use the following:
[tt]
SELECT *
FROM YourTable
WHERE Round(Price,2)-Round(CalculatedPrice,2)<>0
[/tt]
Here, 2 is the decimal precision of your field.

 

[villan60]

Thank you... I suspected that there might be decimals hiding somewhere and I guess I should have thought of using round myself... it works fine now.

thanks for your help

Dax