Null Trouble

[CTOROCK]

I am having trouble with a query returnig a 0 when there is a null. I have a 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);

... But the value is null, there is none with my criteria.
I've tried this:

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

IIf(nz([InspCount]),"0",[InspCount])
or

Count: Nz([InspCount])

I've done this in the same query and tried to create a seperate query that pulls the null info and I never get a thing. Can anyone help? Thanks!

 

[DonQuichote]

There are two problems with Null:

- The Nz function, as you use it, returns an empty string if the field is null. I do not know what 'if "" then ...' does.

- The second is a major bug that microsoft is proud of. It is called 'Null propagation'. It means that Null, used in an expression, will return Null for the whole expression in most of the times. This also implies that comparison operators (=, >, etc.) do NOT HAVE A BOOLEAN RETURN VALUE. Instead, they are variants: They can be True, False OR NULL.
This also works for the iif() function.
Examples:

if &quot;What?&quot;<>Null then print &quot;Clearly different.&quot;

: (nothing happens here)

print not(False)

:

True

print not(Null)

:

Null

print 5+Null

:

Null

Hope this helps