Query trouble with nulls!

[CTOROCK]

I have a query that pulls a count of entries from another query. I later merge that with other queries, but since one of the fields in null I don't get any data at all. I try to prevent this by typing this:

Insp Count: IIf(IsNull([InspCount]),"0",[InspCount])


I type this in the query that it comes from but I still don't get a thing. Can anyone help me turn the value to a zero if null?
thanks!

Eric

 

[thornmastr]

I'm not 100% sure of this, but I think it i what you want

try Insp Count: IIf(nz([InspCount]),"0",[InspCount])

Robert Berman

 

[CTOROCK]

It doesn't work, Do you know why?

 

[zambezibill]

Your original use of IIF should work, so suspect some other issues (query design, typos, etc).

Please explain "doesn't work". Are you getting error messages, incorrect recordset returned? Are you trying to perform an aggregate function (COUNT) at the same time you are trying to convert nulls to zeros (try a 2 step process)?And how are you merging with other queries (UNION query?)

Come back again.

Cheers, Bill

 

[experienced]

CROROCK,

Use Nz function as

Count: Nz([InspCount])

This will return 0. "0" is a string, not 0 (zero).

 

[CTOROCK]

the Final query I am trying to make is called Q_Ytd FundRaisers By Camp. It has data comming from three sources. 1) Tbl_Fundraiser 2)Q_Count-Mini 3) Q_Count-Full

this is the SQL for it:
SELECT DivDist.Division, NewFundraiserDATAENTRY.District, NewFundraiserDATAENTRY.AcctNo, Sum(NewFundraiserDATAENTRY.OrderAmt) AS SumOfOrderAmt, NewFundraiserDATAENTRY.Camp
FROM DivDist INNER JOIN NewFundraiserDATAENTRY ON DivDist.District = NewFundraiserDATAENTRY.District
GROUP BY DivDist.Division, NewFundraiserDATAENTRY.District, NewFundraiserDATAENTRY.AcctNo, NewFundraiserDATAENTRY.Camp;

There are no relationships because I just want the straight values in one line from the sources. Now, I would like to add one more query, but it is null and screws everything up! I want to add Q_Count-Single. The Q_count-full & Mini both return values so it is ok. THis is the SQL to the Q_count-Single:

SELECT Tbl_Fundraiser.Campaign, Count(Tbl_Fundraiser.[Fund Type]) AS SngCount, Tbl_Fundraiser.[Fund Type]
FROM Tbl_Fundraiser
GROUP BY Tbl_Fundraiser.Campaign, Tbl_Fundraiser.[Fund Type]
HAVING (((Tbl_Fundraiser.Campaign)="10&quot AND ((Tbl_Fundraiser.[Fund Type])="single&quot);

The full & mini queries are the same thing. So I need to have the field SngCount to return a 0 if null.
Hope this is a little better explination...Thanks a lot

eric