Query not totaling correctly

[Tara]

I have a totals query that is supposed to sum the balance due for all
receipts from any given customer. The field type is currency. Negative
balance due amounts are stored as ($12.00) for example as opposed to positive
balance due amounts which are stored as $12.00. For example Customer 1 has a
balance due on receipt A of $12.00 and they did not pay it when the receipt
was generated. Then they later paid that balance - tracked with a different
receipt - receipt B. The second receipt then shows a negative ($12.00)
balance since that receipt had no balance due (was only a payment on an
earlier balance due). Receipt A shows a total of $12.00 due. Receipt B
shows a ($12.00) balance due. When I run the query these should cancel each
other out and show a total of $0.00 due. However, it doesn't. It shows
$12.00 due. What am I doing wrong? Would storing the negative balance due
amounts in the -$12.00 format rather than the ($12.00) format work better?
Any help is appreciated!

 

[Jeff Boyce]

Tara

We aren't there. We can't see "how" you are trying to do this.

Please post the SQL statement of the query you are using.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tara]

Okay, here it is. Thanks for looking at it!

SELECT tblReceipts.CustFirst, tblReceipts.CustLast, tblReceipts.Address,
tblReceipts.City, tblReceipts.Date, tblReceipts.LoavesTaken,
tblReceipts.LoavesReturned, tblReceipts.TotalAmtOwed, tblReceipts.AmtPaid,
tblReceipts.Void, tblReceipts.Business, IIf([tblReceipts].[custfirst] Is
Null,"",[tblReceipts].[custfirst] & " ") & IIf([tblReceipts].[custlast] Is
Null,"",[tblReceipts].[custlast] & " ") & IIf([tblReceipts].[business] Is
Null,"",[tblReceipts].[business]) AS CustName, tblReceipts.CustID,
tblReceipts.[Receipt#], Sum(qryOutstandingTOTAL.BalanceDue) AS
SumOfBalanceDue, tblReceipts.AmtStillOwed
FROM qryOutstandingTOTAL INNER JOIN (tblRates INNER JOIN tblReceipts ON
tblRates.RateID = tblReceipts.Rate) ON qryOutstandingTOTAL.CustID =
tblReceipts.CustID
GROUP BY tblReceipts.CustFirst, tblReceipts.CustLast, tblReceipts.Address,
tblReceipts.City, tblReceipts.Date, tblReceipts.LoavesTaken,
tblReceipts.LoavesReturned, tblReceipts.TotalAmtOwed, tblReceipts.AmtPaid,
tblReceipts.Void, tblReceipts.Business, IIf([tblReceipts].[custfirst] Is
Null,"",[tblReceipts].[custfirst] & " ") & IIf([tblReceipts].[custlast] Is
Null,"",[tblReceipts].[custlast] & " ") & IIf([tblReceipts].[business] Is
Null,"",[tblReceipts].[business]), tblReceipts.CustID,
tblReceipts.[Receipt#], tblReceipts.AmtStillOwed
HAVING (((tblReceipts.Void)=No))
ORDER BY tblReceipts.Business;

 

[Tara]

I just posted the SQL statement, but it doesn't matter, I found an error in
data entry that was causing the problem. Fixed it and now it's working fine!

Thanks!