434,917 Members | 1,313 Online
Need help? Post your question and get tips & solutions from a community of 434,917 IT Pros & Developers. It's quick & easy.

# How to use the criteria on a calculated field?

 P: n/a If I have a calculated field, [amount due], calculated from [Total] - [TotalPayment], then on the same query, I used [amount due] > 0. It prompts me for value for [amount due]. I have to use another query, copy everything into the new query then use [amount due] > 0. Is there a better way to do this? Thank you very much, ming. Nov 13 '05 #1
4 Replies

 P: n/a swingingming wrote: If I have a calculated field, [amount due], calculated from [Total] - [TotalPayment], then on the same query, I used [amount due] > 0. It prompts me for value for [amount due]. I have to use another query, copy everything into the new query then use [amount due] > 0. Is there a better way to do this? Thank you very much, ming. ([Total] - [TotalPayment]) > 0 -- [OO=00=OO] Nov 13 '05 #2

 P: n/a swingingming wrote: If I have a calculated field, [amount due], calculated from [Total] - [TotalPayment], then on the same query, I used [amount due] > 0. It prompts me for value for [amount due]. I have to use another query, copy everything into the new query then use [amount due] > 0. Is there a better way to do this? Thank you very much, ming. ([Total] - [TotalPayment]) > 0 -- [OO=00=OO] Nov 13 '05 #3

 P: n/a Hi Travor, It doesn't work. Because I have Sum in the query. Here's the SQL from the Query: SELECT tblCustomers.CustomerID, tblCustomers.CompanyName, Sum(tblOrders.Total) AS TotalOfSum, Sum(qrySumOfPayments.TotalPayments) AS TotalPaymentsOfSum, nz([TotalOfSum])-nz([TotalPaymentsOfSum]) AS AmountDue FROM tblCustomers INNER JOIN (tblOrders LEFT JOIN qrySumOfPayments ON tblOrders.OrderID = qrySumOfPayments.OrderID) ON tblCustomers.CustomerID = tblOrders.CustomerID GROUP BY tblCustomers.CustomerID, tblCustomers.CompanyName; But if there's no better way, I'll stay with my solution for now and throw away elegency. ming Nov 13 '05 #4

 P: n/a Hi Travor, It doesn't work. Because I have Sum in the query. Here's the SQL from the Query: SELECT tblCustomers.CustomerID, tblCustomers.CompanyName, Sum(tblOrders.Total) AS TotalOfSum, Sum(qrySumOfPayments.TotalPayments) AS TotalPaymentsOfSum, nz([TotalOfSum])-nz([TotalPaymentsOfSum]) AS AmountDue FROM tblCustomers INNER JOIN (tblOrders LEFT JOIN qrySumOfPayments ON tblOrders.OrderID = qrySumOfPayments.OrderID) ON tblCustomers.CustomerID = tblOrders.CustomerID GROUP BY tblCustomers.CustomerID, tblCustomers.CompanyName; But if there's no better way, I'll stay with my solution for now and throw away elegency. ming Nov 13 '05 #5

### This discussion thread is closed

Replies have been disabled for this discussion.