cfleming93 cfleming93 - 3 years ago 115
SQL Question

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?

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download