Hi Rod,
Took the Is Null out like you said and did come up with a figure. So I have:
ContractA: DCount("[EmpKey]","tblEmployees","[StartDate]<#01/05/05#" & " AND [LeftDate] Is Null")
ContractB: DCount("[EmpKey]","tblEmployees","(([StartDate] >= #01/05/05#) AND ([StartDate] <=#31/12/05#)) AND [LeftDate] ")
ContractC: DCount("[EmpKey]","tblEmployees","[StartDate]>=#01/01/06#" & " AND [LeftDate] Is Null")
The complete SQL is:
SELECT tblEmployees.EmployeeID, tblDepartment.DepartmentID, tblDepartment.DepartmentName, tblEmployees.StartDate, Count(tblEmployees.EmpKey) AS CountOfempKey, tblPosition.Position, tblGrade.Grade, tblEmployees.Last_Name, tblEmployees.LeftDate, tblEmployees.First_Name, tblEmployees.Last_Name, Date() AS [Date], DCount("[EmpKey]","tblEmployees","[StartDate]<#01/05/05#" & " AND [LeftDate] Is Null") AS ContractA, DCount("[EmpKey]","tblEmployees","(([StartDate] >= #01/05/05#) AND ([StartDate] <=#31/12/05#)) AND [LeftDate] ") AS ContractB, DCount("[EmpKey]","tblEmployees","[StartDate]>=#01/01/06#" & " AND [LeftDate] Is Null") AS ContractC
FROM tblGrade INNER JOIN (tblPosition INNER JOIN (tblDepartment INNER JOIN (tblEmployees INNER JOIN tblJobStatus ON tblEmployees.Job_Status = tblJobStatus.JobStatusNumber) ON tblDepartment.DepartmentID = tblEmployees.DepartmentID) ON tblPosition.PositionID = tblEmployees.PositionID) ON tblGrade.GradeID = tblEmployees.GradeID
GROUP BY tblEmployees.EmployeeID, tblDepartment.DepartmentID, tblDepartment.DepartmentName, tblEmployees.StartDate, tblPosition.Position, tblGrade.Grade, tblEmployees.Last_Name, tblEmployees.LeftDate, tblEmployees.First_Name, tblEmployees.Last_Name
HAVING (((tblEmployees.LeftDate) Is Null))
ORDER BY tblEmployees.Last_Name, tblEmployees.Last_Name;
Still have a syntax problem putting the Is Null in front of the [LeftDate]
What gets me is why nothing is happening when Is Null is added as a criteria in the SQL Table??