Lost in date and time 2

[MrMode]

Dates are recorded in a date/time field in a table and include both the date and time portion.

I want to look at activity for individuals where I count the number of each outcome for each day within a range. So I know I have to format the date/time to return only the date portion.

This is my query at the moment and it works, except it counts each date and time as a separate line rather than dropping the time portion.

SELECT [NM MIS Mnthly call data].AgentName, CDate(Int([underlyingdate])) AS CallDate, [NM MIS Mnthly call data].[Call Outcome], Count([NM MIS Mnthly call data].[Call Outcome]) AS [CountOfCall Outcome], ([UnderlyingDate]) AS DateParameter
FROM [NM MIS Mnthly call data]
GROUP BY [NM MIS Mnthly call data].AgentName, CDate(Int([underlyingdate])), [NM MIS Mnthly call data].[Call Outcome], ([UnderlyingDate])
HAVING ((([NM MIS Mnthly call data].AgentName) Like [enter telemarker's name] & "*") AND ((([UnderlyingDate])) Between [enter start] And [enter end]));

This is the output:

NM MIS Mnthly copy
AgentName CallDate Call Outcome CountOfCall Outcome DateParameter
Agent1 07/05/2013 Appointment - Face to Face 1 07/05/2013
Agent1 07/05/2013 Call at Renewal - Qualified 25 07/05/2013
Agent1 07/05/2013 Call Back - Hot 5 07/05/2013
Agent1 07/05/2013 Call Back - Warm 7 07/05/2013
Agent1 07/05/2013 Happy with current solution 2 07/05/2013
Agent1 07/05/2013 Out of Target market 1 07/05/2013
Agent1 08/05/2013 Call at renewal - Qualified 23 08/05/2013
Agent1 08/05/2013 Call Back - Hot 4 08/05/2013
Agent1 08/05/2013 Call Back - In play 1 08/05/2013 16:14:30
Agent1 08/05/2013 Call Back - In play 1 08/05/2013 16:16:12
Agent1 08/05/2013 Call Back - Warm 9 08/05/2013
Agent1 08/05/2013 Happy with current solution 2 08/05/2013
Agent1 08/05/2013 Out of Target market 3 08/05/2013

If I add the parameter " Between [enter start] And [enter end]" to CallDate:

SELECT [NM MIS Mnthly call data].AgentName, CDate(Int([underlyingdate])) AS CallDate, [NM MIS Mnthly call data].[Call Outcome], Count([NM MIS Mnthly call data].[Call Outcome]) AS [CountOfCall Outcome]
FROM [NM MIS Mnthly call data]
GROUP BY [NM MIS Mnthly call data].AgentName, CDate(Int([underlyingdate])), [NM MIS Mnthly call data].[Call Outcome]
HAVING ((([NM MIS Mnthly call data].AgentName) Like [enter telemarker's name] & "*") AND ((CDate(Int([underlyingdate]))) Between [enter start] And [enter end]));

I get the following error:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. "

Can anyone show me the way out of this problem?

I am trying to reduce the call date field to date only so that I can count the number of outcomes for each date within the range...

 

[PHV]

what about this ?

SELECT AgentName, CDate(Int([underlyingdate])) AS CallDate, [Call Outcome], Count(*) AS [CountOfCall Outcome]
FROM [NM MIS Mnthly call data]
WHERE AgentName Like [enter telemarker's name] & "*" AND (underlyingdate Between [enter start] And [enter end])
GROUP BY AgentName, CDate(Int([underlyingdate])), [Call Outcome]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrMode]

OMG Thanks!

 

[dhookom]

The best way to say thanks is to click the Like This Post? - Star it! link. This recognizes the helper and tells everyone the answer has been found.

One other point to consider faq701-6763.

Duane
Hook'D on Access
MS Access MVP