How do I query a calculated control?

  • Thread starter Thread starter geoffsx
  • Start date Start date
G

geoffsx

I have a table that records a customer's transactions. Each transaction has
a debit and a credit field to record the cost of an item and payments the
customer has made. Each customer will have several credit and debit entries.
I have a calculated control that shows the balance on a form and calculates
the balance by totaling all the debits and all the credits and then
subtracting one from the other. I need to produce a report that only shows
customers whose balance is not null and not zero. What is the simplest way
on achieving this, please?
 
Without knowing your table structure, it is a little difficult, but something
like the following should get you started:

SELECT CustomerID, Sum(NZ([Credit], 0) - NZ([Debit], 0) as Balance
FROM yourTable
GROUP BY CutsomerID
HAVING Sum(NZ([Credit], 0) - NZ([Debit], 0)) <> 0


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Back
Top