Help with SQL null value

kevinh2320

Board Regular
Joined
May 13, 2016
Messages
51
I have the following query in my MS Access db. The problem I'm having is when no AmtDue (amount due) has been received the query just returns as blank. I want is for the query to return as 0 in cases when no AmtDue has been received. Here's what I have so far. Any suggestions on how to modify this query to achieve that or, a new query would be appreciated.

SELECT Sum([SalesTbl].AmtDue) AS SumOfAmtDue, [SalesTbl].AmtReceived
FROM 906kTbl
GROUP BY [SalesTbl].AmtReceived
HAVING ((([SalesTbl].AmtReceived)="YES"));
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,628
Office Version
2013
Platform
Windows
In Access you can use the Nz() function for this, so:

SQL:
SELECT Sum(Nz([SalesTbl].AmtDue, 0)) AS SumOfAmtDue, [SalesTbl].AmtReceived
FROM 906kTbl
GROUP BY [SalesTbl].AmtReceived
HAVING ((([SalesTbl].AmtReceived)="YES"));
 

Watch MrExcel Video

Forum statistics

Threads
1,095,727
Messages
5,446,168
Members
405,388
Latest member
Arlind

This Week's Hot Topics

Top