Excluding values of 0

[annysjunkmail]

I am trying to get a query to return records to show outstanding
balances which greater are than 0. When I add >0 as criteria I get a
popup box asking for TotalOrders?

I have worked at it for ages but can't work it out.
Can someone help please.
Here is my SQL

SELECT [Quantity]*[UnitPrice] AS TotalOrders, Payments.PaymentAmount,
[TotalOrders]-[PaymentAmount] AS Balance
FROM (Orders LEFT JOIN Payments ON Orders.OrderID = Payments.OrderID)
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE ((([TotalOrders]-[PaymentAmount])>0));



Many thanks
Tony

 

[JohnFol]

Total Orders is not a field, it is an alias.
Try

( ( [Quantity]*[UnitPrice]) -[PaymentAmount] ) >0

 

[annysjunkmail]

Very good John,

I have added your suggested code and the SQL now looks like this

SELECT [Quantity]*[UnitPrice] AS TotalOrders, Payments.PaymentAmount,
[TotalOrders]-[PaymentAmount] AS Balance,
(([Quantity]*[UnitPrice])-[PaymentAmount])>0 AS Expr1
FROM (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID) LEFT JOIN Payments ON Orders.OrderID =
Payments.OrderID
WHERE ((((([Quantity]*[UnitPrice])-[PaymentAmount])>0)<>0));

I think this is what you meant when you said try this...
I have added <>0 to remove zeros and it seems to work correctly. If
this is wrong could you please post back.
Thanks for your help

Tony