Query filter for calculated field

[Stefan]

In the query I have a calculated field

Remainder:[Billed Amount]-([Insurance Amount]+[Client
Amount])

in the criteria box for remainder I have <>0 to filter out
claims with no balance. The filter does not work.

How can I filter these records out???

Insurance Amount and Client Amount are defined as double
in the table. This is a linked table from my billing
software so I cannot change settings in the table.

If I do not set Remainder to currency some of the
calculations are very large numbers

ex 136788005E-15 If I set remainder to currency it will
return $0.00 for these fields.

Even when Remainder is currency and $0.00 and I have <>0
in criteria box the $0.00 records are not filtered out.



Thanks in advance

 

[John Vinson]

How can I filter these records out???

Insurance Amount and Client Amount are defined as double
in the table. This is a linked table from my billing
software so I cannot change settings in the table.

If I do not set Remainder to currency some of the
calculations are very large numbers

ex 136788005E-15 If I set remainder to currency it will
return $0.00 for these fields.

Even when Remainder is currency and $0.00 and I have <>0
in criteria box the $0.00 records are not filtered out.

The Double is the source of the problem. What is the billing software?

The problem is that Doubles (and Floats) are APPROXIMATIONS. Just as
1/7 cannot be expressed exactly as a decimal number, so 0.1 cannot be
expressed exactly as a Double; subtracting will often (even usually!)
give a roundoff error in the range you see (10^-15 or so times the
values subtracted).

If you cannot change the Datatype of the fields to Currency (note: a
currency FORMAT is irrelevant; it's a currency DATATYPE that matters)
you may want to use a criterion:

Remainder:Abs([Billed Amount]-([Insurance Amount]+[Client
Amount]))

and use a criterion of > 1.0E-10. The Abs function will provide a
positive number (whether the roundoff is positive or negative), and
the criterion will exclude those records that are "close" to zero.