Query Calculation

[mtompkins]

I have the following query:

SELECT [Inventory Transactions].TransactionDescription, Products.ProductName, [Inventory Transactions].UnitsReceived, [UnitsReceived]-[Qty] AS QtyAvail, qryCompOut.Qty
FROM Products INNER JOIN (qryCompOut RIGHT JOIN [Inventory Transactions] ON qryCompOut.comp = [Inventory Transactions].TransactionDescription) ON Products.ProductID = [Inventory Transactions].ProductID
GROUP BY [Inventory Transactions].TransactionDescription, Products.ProductName, [Inventory Transactions].UnitsReceived, [UnitsReceived]-[Qty], qryCompOut.Qty
HAVING ((([Inventory Transactions].UnitsReceived)>0));

which produces the intended calculation except in instances where there is no data (NULL) for "Qty". Can someone please help me produce the calculation in these cases (i.e. Qty should revert to a zero in the case of null).

Thanks

 

[PHV]

Replace this:
[UnitsReceived]-[Qty]
By this:
[UnitsReceived]-Nz([Qty],0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mtompkins]

Thanks - that was it. Hate when you forget the "easy" ones.