Hello all. I am running a query dependent on 4 queries which calculates turnover by counting the number of hires, the number of terms, suming the counts and dividing term/hires. For some months there are no terms and the query that counts the number of terms returns nothing (just the grey field names) How do I get this query to return zero for these instances so when I run the last query, I get 0%? I tried to use the Nz function but I am not using it correctly. Here is the count portion of my code:

strSQL = "SELECT Count(tbl_Employee.enumberID) AS CountofenumberID, tbl_Market .marketID FROM ......;

Going forward, I append the results from the final query to a table and for the months where there is no turnover the hire information does not get appended. I need these months to appear since I append the number of terms, hires, and the corresponding market and date. For example:

Terms Hires Market Month
12 36 AR14 Jun-04
0 15 AR14 Jul-04


Currently, the second record does not appear since the query returns nothing or Null.

Thanks for your help!!!

The only certain way I know how to do this is to turn your query into a recordset e.g.

Dim rst_data as recordset
Dim dbl_answer as long
.....

set rst_data = currentdb.openrecordset("sqlstr.....")
dbl_answer = rst_data.recordcount

....

set rst_data = nothing

and then pass dbl_answer into your final table. If there are no records, dbl_answer will have the value 0, just as, I think, you wanted. This is DAO, but works equally well in any version of Access if you have the references selected

Cheers