How do I query a calculated control?

[geoffsx]

I have a customer table and linked transaction table. The transaction table
has fields for the transaction date, the item description, the cost (debit)
and amount paid (credit). Customers will have many transactions. On the input
form, the total outstanding balance is a calculated field based on the
calculation: Sum[TRcredit] - Sum[TRdebit] ie, it adds all the credits and
debits and then subtracts one from the other, for each customer.
I need to report those customers whose balance is not zero. It could be a
positive, negative or null value.
Help would be much appreciated.

 

[Dale Fye]

I'm not sure how your balance could be NULL.
Still don't know very much about your table structure, but with what you've
given me, try something like:

SELECT C.CustomerNameLast, C.CustomerNameFirst,
SUM(NZ(T.TRCredit, 0) - NZ(T.TRDebit, 0)) as Balance
FROM tblCustomers as C
INNER JOIN tblTransactions as T
ON C.CustomerID = T.CustomerID
GROUP BY C.CustomerNameLast, C.CustomerNameFirst
HAVING SUM(NZ(T.TRCredit, 0) - NZ(T.TRDebit, 0)) <> 0

HTH
Dale