Trouble With Nz and IIF Is Null in Query.

[CTOROCK]

I can't seem to get this to work if it is null. I've tried the Nz of IIf is null, but that doesn't seem to work. I thought that Count would return a zero if null to. Can anyone help me on how I can prevent nulls in this query so it can return a 0? This is what I have:


SELECT Count([Q_Team Summary].Container) AS [Air Containers], Sum([Q_Team Summary].CartonsUnld) AS [Air Unloaded], [Q_Team Summary].Type, Sum([Q_Team Summary].CARTONS) AS SumOfCARTONS, Sum([Q_Team Summary].[CARTONS DONE]) AS [SumOfCartons Done], Sum(Abs([Delayed])) AS StartedNotFinished, Sum([Q_Team Summary].[Total Time]) AS [SumOfTotal Time], Sum([Q_Team Summary].SumOfSkus) AS Skus, ([SumOfcartons Done]/700)+(([skus]*3)/60) AS Projected
FROM [Q_Team Summary]
GROUP BY [Q_Team Summary].Type
HAVING ((([Q_Team Summary].Type)="Air&quot);

Thanks in advance "The greatest risk, is not taking one."

 

[megaamp]

SELECT Sum(IIf(IsNull([Q_Team Summary].Container),0,1) AS [Air Containers], Sum([Q_Team Summary].CartonsUnld) AS [Air Unloaded], [Q_Team Summary].Type, Sum([Q_Team Summary].CARTONS) AS SumOfCARTONS, Sum([Q_Team Summary].[CARTONS DONE]) AS [SumOfCartons Done], Sum(Abs([Delayed])) AS StartedNotFinished, Sum([Q_Team Summary].[Total Time]) AS [SumOfTotal Time], Sum([Q_Team Summary].SumOfSkus) AS Skus, ([SumOfcartons Done]/700)+(([skus]*3)/60) AS Projected
FROM [Q_Team Summary]
GROUP BY [Q_Team Summary].Type
HAVING ((([Q_Team Summary].Type)="Air&quot);

 

[r937]

where exactly is the null happening?

by the way, that particular HAVING condition is best moved to the WHERE clause

rudy

 

[CTOROCK]

Well, when there aren't any records with the Type = "Air", Iget nothing, I would like it to have 0 if nothing is there.
I've tried MegaAmp's and I get a syntax error where the AS is? Any suggestions?
Thanks for your time. "The greatest risk, is not taking one."