Adding

[KenC]

Can someone explain to me why Access does not appear to
always add correctly?

I have an accounting file I work on and create the
following fields in my query;
AcctNbr
AcctNm
DrRecs: IIf(Amt >= 0, 1, 0)
DrAmt: IIf(Amt >= 0, Amt, 0)
CrRecs: IIf(Amt < 0, 1, 0)
CrAmt: IIf(Amt < 0, Amt, 0)
TotRecs: IIf(Amt, 0, 1, 0)
TotAmt: IIf(Amt, Amt, 0)

It isn't uncommon for me to get the correct answers in the
first 4 fields. But almost always I get the wrong answers
in the last 2 fields. Here are 2 examples;

100100 Bank1 24 $2,806,316.11
7,515 ($2.870.160.44)
7,539 ($63,844.30)

100200 Bank2 701 $169,493,971.79
14,798 ($166,121,438.69)
15,499 $3,372,533.77

Here the number of records totaled out correctly.
However, the amounts are obviously not right. Agreed that
the differences are minor, but they are wrong. If I
change my query and substitute -0.001 for the first zero
in each field the results are then correct. Why is this?
Also, this isn't an isolated case. This tends to happen
on all of the different accounting files I use.

An explanation would be appreciated, if one exists.

 

[Lynn Trapp]

It has nothing to do with Access. It has to do with the way the Intel
processor handles floating point numbers. Consider changing your datatype
to Currency.

 

[John Vinson]

Can someone explain to me why Access does not appear to
always add correctly?

I have an accounting file I work on and create the
following fields in my query;
AcctNbr
AcctNm
DrRecs: IIf(Amt >= 0, 1, 0)
DrAmt: IIf(Amt >= 0, Amt, 0)
CrRecs: IIf(Amt < 0, 1, 0)
CrAmt: IIf(Amt < 0, Amt, 0)
TotRecs: IIf(Amt, 0, 1, 0)
TotAmt: IIf(Amt, Amt, 0)

It isn't uncommon for me to get the correct answers in the
first 4 fields. But almost always I get the wrong answers
in the last 2 fields. Here are 2 examples;

Well, the last two fields make no sense to me. IIF takes three
arguments: the first is a logical expression, which evaluates to True
(nonzero) or False (zero). Your expression for TotRecs has *four*
arguments not three; and both of them use Amt as if it were a logical
expression rather than a value.

100100 Bank1 24 $2,806,316.11
7,515 ($2.870.160.44)
7,539 ($63,844.30)

100200 Bank2 701 $169,493,971.79
14,798 ($166,121,438.69)
15,499 $3,372,533.77

Here the number of records totaled out correctly.
However, the amounts are obviously not right. Agreed that
the differences are minor, but they are wrong. If I
change my query and substitute -0.001 for the first zero
in each field the results are then correct. Why is this?
Also, this isn't an isolated case. This tends to happen
on all of the different accounting files I use.

As suggested, use a Currency datatype rather than a Float. Floats are
limited to approximately 7 digits precision, with roundoff error.
Currency (not Currency format, but the Currency *datatype*) is a
scaled huge integer with a range into the trillions, exactly four
decimals, and NO roundoff error.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps