CODEWITHSUNDEEP
sqlms-access
cfleming93
cfleming93

Reputation: 219

Access SQL Calculation in SELECT make not null

I have a query that filters results for products which have had orders sent after an user-input date, and calculates what the quantity becomes if the order was sent after that date.

SELECT id, ProductName,
   [OnHand]+ SUM([OrderJoin.Quantity]) AS Qty After
FROM Query3
WHERE Query3.ShippedDate > [Enter End Date] And
  Query3.ShippedDate) Is Not Null
GROUP BY id, ProductName, OnHand;

But if I were to remove the WHERE statement, how would I make it so the Qty After would show as OnHand for the results that become NULL?

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You would use NZ() to convert the NULL value to 0. Something like this:

SELECT id, ProductName,
       NZ(OnHand, 0) + NZ(SUM([OrderJoin.Quantity]), 0) AS QtyAfter
...

Upvotes: 4

Related Questions