Help with IIF statement and Null Values 1

[kentwoodjean]

I have a hard time formatting the IIF statements, as a matter of fact I normally do not use them because of that. I find that I now have a need and could uses a bit of help. It is an Append Query that calculates the count of contracts in inventory for 5 different RespCdes. What I want to have is a 0 instead of a blank when there is a null value. Below is the SQL of the Append Query:

INSERT INTO InventoryRespID ( FromDate, EndingInven, RespID )
SELECT IIf(Weekday(Date())=2,Date()-3,Date()-1) AS FromDate, Count(tblLastUpdated.[Contract #]) AS EndingInven, RespCdeLst.RespID
FROM tblLastUpdated INNER JOIN RespCdeLst ON tblLastUpdated.[Resp Cd] = RespCdeLst.RespCde
GROUP BY IIf(Weekday(Date())=2,Date()-3,Date()-1), RespCdeLst.RespID, tblLastUpdated.[Closed Dt]
HAVING (((tblLastUpdated.[Closed Dt]) Is Null));

 

[lupins46]

I don't see where your blanks are coming from unless you have your field formatted to show zeros as blanks. Count() will show zero(0) if there are no non-null values.

 

[ESquared]

Not sure which field you're getting a "blank in" but in the meantime you might check out the Nz() function.

[tt]Nz(Expression, ValueIfNull)[/tt]

works just like

[tt]Iif(Expression Is Null, ValueIfNull, Expression)[/tt]

 

[kentwoodjean]

I used the NZ expression in my query and everything works great now. As I look back on my original post I guess it was not real clear ESquared gave me what I needed. Thanks so much.