Using Nz with the count function

[Tony White]

Hello
I have a query that counts the number of employees that were hired and left
within a certain date range. I take the answers from each query and append
the results to a table. When no employees were hired or no employees left, I
get null values. I tried to add Nz at the beginning of the SQL but I still
get the "no records" view (just the grey box listing the field name). I think
I am using the Nz function in the wrong place since the SQL does not bomb
out. Here is my code:

qryCountofTermsByPosition

SELECT Nz(Count(tbl_Employee.eNumberID),0) AS CountOfeNumberID
FROM ((tbl_Market INNER JOIN tbl_Group ON tbl_Market.marketID =
tbl_Group.marketID) INNER JOIN (tbl_Region INNER JOIN tbl_Department ON
tbl_Region.regionID = tbl_Department.regionID) ON tbl_Group.groupID =
tbl_Region.groupID) INNER JOIN ((tbl_Termination INNER JOIN tbl_Employee ON
tbl_Termination.termReason = tbl_Employee.termReason) INNER JOIN (tbl_Job
INNER JOIN tbl_EmployeeJobs ON tbl_Job.jobCodeID =
tbl_EmployeeJobs.jobCodeID) ON tbl_Employee.eNumberID =
tbl_EmployeeJobs.eNumberID) ON tbl_Department.rcID = tbl_EmployeeJobs.rcID
GROUP BY tbl_Employee.recentHireDate, tbl_Employee.termDate,
tbl_Termination.termType, tbl_Employee.resi, tbl_EmployeeJobs.Category,
tbl_Job.revisedJobTitle, tbl_Market.marketID, tbl_EmployeeJobs.schedHrs
HAVING (((tbl_Employee.recentHireDate)>=#12/1/2003# And
(tbl_Employee.recentHireDate)<=#12/31/2003#) AND
((tbl_Employee.termDate)>=#12/1/2003# And
(tbl_Employee.termDate)<=#1/31/2004#) AND ((tbl_Termination.termType)='V')
AND ((tbl_EmployeeJobs.Category)='P' Or (tbl_EmployeeJobs.Category)='Q' Or
(tbl_EmployeeJobs.Category)='F') AND ((tbl_Job.revisedJobTitle)='Teller') AND
((tbl_Market.marketID)='PHL') AND ((tbl_EmployeeJobs.schedHrs)>30)) OR
(((tbl_Employee.recentHireDate)>=#12/1/2003# And
(tbl_Employee.recentHireDate)<=#12/31/2003#) AND
((tbl_Employee.termDate)>=#12/1/2003# And
(tbl_Employee.termDate)<=#1/31/2004#) AND ((tbl_Employee.resi) Is Not Null)
AND ((tbl_EmployeeJobs.Category)='P' Or (tbl_EmployeeJobs.Category)='Q' Or
(tbl_EmployeeJobs.Category)='F') AND ((tbl_Job.revisedJobTitle)='Teller') AND
((tbl_Market.marketID)='PHL') AND ((tbl_EmployeeJobs.schedHrs)>30));

As a work around, after I append all the records to the table, I run a
subroutine that updates all null values to zero.
Thank you for your help!!!
Tony

 

[[MVP] S.Clark]

I think what you need to understand is that the query is not returning a
Null value, it's returning zero records. If it we're returning 1 record
with a Null value, THEN you could convert it to zero, but you can't convert
something that's not there.

 

[Tony White]

I understand that in this case, I have no records that match the crieteria.
However, I need to reflect in a table for this instance, there is no hire.
Should I insert the SQL into the querydef and then open the recordset and
count the records, finding non, write a seperate action query to do a custom
append?:

strSQL ="SELECT Count(......."

qfd.SQL = strSQL

rec = qdf.OpenRecordset("qryName",dbOpendynaset)

Debug.Print rec.RecordCount

if rec.RecordCount = 0 then ....

At this point should I write an action query to specifically handle this
situation?

strQuery ="INSERT INTO Blah (field1, field2,......"