Improper WHERE Clause

[LWGalvan]

I have a query where I want to return certain information for all users, including the number of calls after a particular date (actually it will be BETWEEN dates, but while I've been trying to figure this out, I've just been using > than.)

My query is as follows:

SELECT tblAgentInfo.AgentNum AS AgentID, MAX(tblAgentInfo.Dept) AS Dept, Max(tblAgentInfo.LName & ", " & tblAgentInfo.FName) AS FullName, MAX([tblMgtStaff].[LName] & ", " & [tblMgtStaff].[FName]) AS Sup, max(tblAgentInfo.Role) AS Role, max(tblAgentInfo.HireDate) AS HireDate, Count(tblCSMonitors.CallDate) AS TheCount
FROM tblMgtStaff INNER JOIN (tblAgentInfo LEFT JOIN tblCSMonitors ON tblAgentInfo.AgentNum = tblCSMonitors.Agent) ON tblMgtStaff.ID = tblAgentInfo.Sup
WHERE tblAgentInfo.Dept<>'999' AND (tblCSMonitors.CallDate>Format(DateAdd("m", -3, Now()), "mm/01/yyyy"))
GROUP BY tblAgentInfo.AgentNum
ORDER BY MAX(tblAgentInfo.Dept) ASC;

Everything works as expected (all agents are returned with counts ranging from 0 to N) until I add the date parameter. I know it must be because I'm adding it to the WHERE clause, but I'm not sure where else I can add it. I tried getting cute and adding it to COUNT(tblCSMonitors.CallDate > Format(DateAdd("m", -3, Now()), "mm/01/yyyy")) AS TheCount, but of course that didnt work. Now all I get are agents who had calls after that date - my zero results are no longer included.

Any ideas on where I can fix this?

 

[LWGalvan]

Found the answer at this site - sort of.
http://www.bennadel.com/blog/579-SQL-COUNT-NULLIF-Is-Totally-Awesome.htm

Here is my final query. Hope it can help someone in the future.

SELECT tblAgentInfo.AgentNum AS AgentID, MAX(tblAgentInfo.Dept) AS Dept, Max(tblAgentInfo.LName & ", " & tblAgentInfo.FName) AS FullName, MAX([tblMgtStaff].[LName] & ", " & [tblMgtStaff].[FName]) AS Sup, max(tblAgentInfo.Role) AS Role, max(tblAgentInfo.HireDate) AS HireDate,
abs(sum(tblCSMonitors.CallDate>=format(now(),"mm/01/yyyy"))) as TheCount
FROM tblMgtStaff INNER JOIN (tblAgentInfo LEFT JOIN tblCSMonitors ON tblAgentInfo.AgentNum = tblCSMonitors.Agent) ON tblMgtStaff.ID = tblAgentInfo.Sup
WHERE tblAgentInfo.Dept<>'999'
GROUP BY tblAgentInfo.AgentNum
ORDER BY MAX(tblAgentInfo.Dept) ASC;