Displaying Count if Null value exists

[adams.bria]

Code:

SELECT stu_assn.DC_ID, stu_assn.Occ_ID, Count(stu_assn.Occ_ID) AS CountOfOcc_ID, Dept_Request.Num_Req, [Num_Req]-[CountOfOcc_ID] AS Num_Needed
FROM ((stu_assn INNER JOIN Dept_Cont ON stu_assn.DC_ID = Dept_Cont.DC_ID) INNER JOIN Local_Vars ON Dept_Cont.Aid_Year = Local_Vars.Aid_Year) INNER JOIN Dept_Request ON Dept_Cont.DC_ID = Dept_Request.DC_ID
WHERE (((Dept_Request.Occ_ID)=[stu_assn].[occ_ID]) AND ((Dept_Cont.aid_year)=[local_vars].[aid_year]))
GROUP BY stu_assn.DC_ID, stu_assn.Occ_ID, Dept_Request.Num_Req;

So what I want to happen is even if CountOfOcc_ID is null, if an entry exists for dept_Request.Num_Req I want it to show 0 for the countofocc_ID and display in the query.

Any ideas?

[gopherking]

Not sure if you can do this in a Query, but rather than Count(stu_assn.Occ_ID), can you use Nz(Count(stu_assn.Occ_ID),0)?

This should return a "0" when the Count = Null. Worth a shot ...

[adams.bria]

That unfortunetly did not work. Anyone else got an idea?

[gopherking]

adams.bria,

I was able to find a location where I was able to use the Nz([field],0) in a query. I am using it on a sum field, which is summed in another query and the Nz([field],0) is applied to the resulting field from that query. So, to get around this issue, if you can create a query that does your Count for you, then in the above query, if you reference the already Counted field in the new query, the Nz([field],0) should work as expected.

It's a little out of the way, but it should get you what you need.

gopherking