Running a Crosstab Query for a specific date range (1 Viewer)

[jol]

I want to be able to run a query that provides the results for a specific date range. I've been able to do this for a simple query using:
SELECT [Contact Tracker].[Date of Contact], [Contact Tracker].[Type of Contact], [Contact Tracker].[Contact Name]
FROM [Contact Tracker]
GROUP BY [Contact Tracker].[Date of Contact], [Contact Tracker].[Type of Contact], [Contact Tracker].[Contact Name]
HAVING ((([Contact Tracker].[Date of Contact]) Between [EnterStartDate] And [EnterEndDate]))
ORDER BY [Contact Tracker].[Type of Contact], [Contact Tracker].[Contact Name];


but when I try to do the same for a Crosstab query, I get error messages.

Here is my Crosstab query:
TRANSFORM Count([Contact Tracker].[ID]) AS CountOfID
SELECT [Contact Tracker].[Contact Name], Count([Contact Tracker].[ID]) AS [Total Of ID]
FROM [Contact Tracker]
GROUP BY [Contact Tracker].[Contact Name]
PIVOT [Contact Tracker].[Type of Contact];

I'd appreciate any advice!

 

[theDBguy]

Hi. Welcome to AWF!

What did the error message say?

 

[jol]

Hi. Welcome to AWF!

What did the error message say?

Thanks for the welcome! I'm happy to have this resource.

I've tried it multiple different ways and have gotten a multitude of errors.
If I try to add the HAVING statement above after the GROUP BY, it says "Syntax Error in TRANSFORM statement."
If I try adding between [EnterStartDate] and [EnterEndDate] under Criteria in the Design View:

TRANSFORM Count([Contact Tracker].ID) AS CountOfID
SELECT [Contact Tracker].[Contact Name], Count([Contact Tracker].ID) AS [Total Of ID]
FROM [Contact Tracker]
WHERE (([Contact Tracker].[Date of Contact] Between [EnterStartDate] And [EnterEndDate]))
GROUP BY [Contact Tracker].[Contact Name]
PIVOT [Contact Tracker].[Type of Contact];

it says "The Microsoft Access database engine does not recognize [EnterStartDate] as a valid field name or extension."

 

[ebs17]

PARAMETERS EnterStartDate Date, EnterEndDate Date;
TRANSFORM Count(ID) AS CountOfID
SELECT [Contact Name], Count(ID) AS [Total Of ID]
FROM [Contact Tracker]
WHERE [Date of Contact] Between [EnterStartDate] And [EnterEndDate]
GROUP BY [Contact Name]
PIVOT [Type of Contact];

 

[jol]

PARAMETERS EnterStartDate Date, EnterEndDate Date;
TRANSFORM Count(ID) AS CountOfID
SELECT [Contact Name], Count(ID) AS [Total Of ID]
FROM [Contact Tracker]
WHERE [Date of Contact] Between [EnterStartDate] And [EnterEndDate]
GROUP BY [Contact Name]
PIVOT [Type of Contact];

Thanks. When I tried this, I get the error:
Malformed GUID in query expression '[Date of Contact] Between [EnterStartDate] and [EnterEndDate]'.

 

[ebs17]

I have no idea where a GUID should be needed in a date field, and I don't know your table either.

 

[jol]

I have no idea where a GUID should be needed in a date field, and I don't know your table either.

I appreciate your attempt to help. I don't know where a GUID would be needed either. My table is quite simple.

 

[jol]

I got it to work with this SQL:
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Count([Contact Tracker].ID) AS CountOfID
SELECT [Contact Tracker].[Contact Name]
FROM [Contact Tracker]
WHERE ((([Contact Tracker].[Date of Contact]) Between [start date] And [end date]))
GROUP BY [Contact Tracker].[Contact Name]
PIVOT [Contact Tracker].[Type of Contact];

Thanks for the help!