If statement?? 2

[acwendy60]

In SQL statement, what do I do if I want to
1.check a variable if it's 0
2. if it is, set it to 0

SELECT tblInventoryTransaction.ProductID, Sum(tblInventoryTransaction.UnitsReceived)-Sum(tblInventoryTransaction.UnitsSold)-Sum(tblInventoryTransaction.UnitsShrinkage) AS OnHand, Sum([UnitsOrdered])-Sum([UnitsReceived]) AS [On Order]
FROM tblInventoryTransaction
WHERE (((tblInventoryTransaction.cancell)=No))
GROUP BY tblInventoryTransaction.ProductID;

If [On Hand] is less than 0, then I want to set it to 0

 

[PHV]

You may try something like this:
SELECT ProductID, IIf(Sum(UnitsReceived-UnitsSold-UnitsShrinkage)<0,0,Sum(UnitsReceived-UnitsSold-UnitsShrinkage)) AS OnHand, Sum(UnitsOrdered-UnitsReceived) AS [On Order]
FROM tblInventoryTransaction
WHERE cancell=No
GROUP BY ProductID;


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

 

[traingamer]

iif(yourexpr < 0, 0, yourexpr)

traingamer