Set a Null Value to Display "0"

[WatsonDyar]

I need the following to display a zero if a null value is returned:

SELECT tdsIndivData.dsReportID, Count(tdsIndivData.StaffID) AS CountOfStaffID
FROM tdsReportData INNER JOIN tdsIndivData ON tdsReportData.dsReportID = tdsIndivData.dsReportID
WHERE (((tdsIndivData.Availability)="75% Availability"))
GROUP BY tdsIndivData.dsReportID;


Any ideas?

 

[JHB]

Use the NZ function.
Here is a link to the description of the function + some samples.
http://office.microsoft.com/en-us/access-help/nz-function-HA001228890.aspx

 

[WatsonDyar]

Thank you JHB!

I've tried this:

SELECT tdsIndivData.dsReportID, Nz(Count(tdsIndivData.StaffID),0) AS CountOfStaffID
FROM tdsReportData INNER JOIN tdsIndivData ON tdsReportData.dsReportID = tdsIndivData.dsReportID
WHERE (((tdsIndivData.Availability)="75% Availability"))
GROUP BY tdsIndivData.dsReportID;

However, I still get the null value!

 

[bob fitz]

Perhaps you need:

SELECT tdsIndivData.dsReportID, Count(Nz(tdsIndivData.StaffID),0) AS CountOfStaffID
FROM tdsReportData INNER JOIN tdsIndivData ON tdsReportData.dsReportID = tdsIndivData.dsReportID
WHERE (((tdsIndivData.Availability)="75% Availability"))
GROUP BY tdsIndivData.dsReportID;

 

[WatsonDyar]

Hi Bob. Thanks so much.

I tried this as well. I get a "wrong number of arguments...." error pertaining to the change in the "Count(Nz...." sytax.

 

[WatsonDyar]

Okay, so got some help from another member. The trick was to use the Nz function in the subreport instead of the query. Worked great!

Thanks all!