Null Value = 0 in Count query

[Tigerdude]

I am creating a query that counts records based on different criteria(Compound statements in SQL). As long as there is a record of desired type, all is well. However if there are no records that match, then query returns null.This does not help when creating other queries and reports. (I need 0). I've tried IIf(IsNull[],0,[]) and Nz with no luck. Suggestions welcomed.

 

[AshleyRickards]

This might work:

FindCount:Count(IIf(IsNull([RELSCORCAT]),1,1))

I use this to return records that are not scored. My results look like:
RELSCORCAT SumOfPRERELAMOU FindCount
0 10

HTH Ashley L Rickards
SQL DBA

 

[CosmoKramer]

Can you post the SQL for this query? It would probably help to see one of those "compound statements".....

 

[Tigerdude]

Below is the code I'm working with. If there are no students that meet the criteria, then I get a Null value; however going forward I need a 0.

SELECT tblPopulatedStudents.EthnicityCD, tblPopulatedStudents.PartCD, Count(tblPopulatedStudents.EthnicityCD) AS HawPac
FROM tblPopulatedStudents
GROUP BY tblPopulatedStudents.EthnicityCD, tblPopulatedStudents.PartCD
HAVING (((tblPopulatedStudents.EthnicityCD)="6&quot AND ((tblPopulatedStudents.PartCD)="1" Or (tblPopulatedStudents.PartCD)="2&quot);

 

[CosmoKramer]

Try this, I think the syntax is correct:

SELECT tblPopulatedStudents.EthnicityCD, tblPopulatedStudents.PartCD, IIf (IsNull(Count(tblPopulatedStudents.EthnicityCD)),0,Count(tblPopulatedStudents.EthnicityCD)) AS HawPac
FROM tblPopulatedStudents
GROUP BY tblPopulatedStudents.EthnicityCD, tblPopulatedStudents.PartCD
HAVING (((tblPopulatedStudents.EthnicityCD)="6&quot AND ((tblPopulatedStudents.PartCD)="1" Or (tblPopulatedStudents.PartCD)="2&quot);