multiple calcs in a query

[Guest]

I have created a select query to start with to calculate the balance on an
invoice. The query uses only one table (OpenAR). I have a calculation which
takes the Inovice amount and subtracts the payment amount and then the
adjustment amount to give me an open balance. The problem that I have is with
a record which ends up with a zero balance. If all three fields are populated
and the result is dispalyed in a select query it correctly shows zero. When I
change the query to a make table query with all the same fields etc. (simply
by clicking on thw queries tab and selecting make table query and filling in
the name) the balance shows up with a result like: -1.95399252334028E-14. If
only two fields are filled in the calculation works fine. The problem seems
to only be with zero balance results. The problem is when I print a report
showing the open balance and it shows up like the example I provided it
confuses people reading the report. Any suggestions?

 

[Gary Walter]

I have created a select query to start with to calculate the balance on an
invoice. The query uses only one table (OpenAR). I have a calculation
which
takes the Inovice amount and subtracts the payment amount and then the
adjustment amount to give me an open balance. The problem that I have is
with
a record which ends up with a zero balance. If all three fields are
populated
and the result is dispalyed in a select query it correctly shows zero.
When I
change the query to a make table query with all the same fields etc.
(simply
by clicking on thw queries tab and selecting make table query and filling
in
the name) the balance shows up with a result like: -1.95399252334028E-14.
If
only two fields are filled in the calculation works fine. The problem
seems
to only be with zero balance results. The problem is when I print a report
showing the open balance and it shows up like the example I provided it
confuses people reading the report. Any suggestions?

It sounds like you are using float type instead of Currency...

When Access Math Doesn't Add Up
by Luke Chung
http://www.fmsinc.com/tpapers/math/index.html

 

[Jamie Collins]

It sounds like you are using float type instead ofCurrency...

When Access Math Doesn't Add Up
by Luke Chunghttp://www.fmsinc.com/tpapers/math/index.html- Hide quoted text -

Note that article relates to VBA, not queries e.g. example 1:

SELECT 100.8 - 100.7

correctly returns 0.1 because, unlike VBA, Access/Jet SQL native
decimals are fixed point, not floating point.

Jamie.

--