Specifying a date range in a crosstab/pivot query

  • Thread starter Thread starter Matt Braymiller
  • Start date Start date
M

Matt Braymiller

Greetings:
Below is a short SQL statement for a query to show how many calls we
received from each customer for the current date.

TRANSFORM Count([qry-WO].[WO-Data_WO-Number]) AS
[CountOfWO-Data_WO-Number]
SELECT [qry-WO].[S-Data_Store-Type],
Count([qry-WO].[WO-Data_WO-Number]) AS [Total Calls]
FROM [qry-WO]
WHERE ((([qry-WO].[WO-Data_Date-Issued])=Date()))
GROUP BY [qry-WO].[S-Data_Store-Type]
PIVOT [qry-WO].[WO-Data_DWM-Contact];

Is there a way to allow a date range in the WHERE clause? I have
tried: WHERE ((([qry-WO].[WO-Data_Date-Issued])=(Between [Enter First]
and [Enter Last])), but Access doesn't like the syntax. I can work
around it if I use a make table query to return records for the
specified dates and then run the crosstab on that table.
 
Is there a way to allow a date range in the WHERE clause? I have
tried: WHERE ((([qry-WO].[WO-Data_Date-Issued])=(Between [Enter First]
and [Enter Last])), but Access doesn't like the syntax. I can work
around it if I use a make table query to return records for the
specified dates and then run the crosstab on that table.
Click to expand...

You can use parameters in a crosstab query - but in order to do so you
must (unlike other queries, where you *may*) specify the Paramters
collection:

PARAMETERS [Enter Start Date:] Date, [Enter End Date:] Date;
TRANSFORM Count([qry-WO].[WO-Data_WO-Number]) AS
[CountOfWO-Data_WO-Number]
SELECT [qry-WO].[S-Data_Store-Type],
Count([qry-WO].[WO-Data_WO-Number]) AS [Total Calls]
FROM [qry-WO]
WHERE ((([qry-WO].[WO-Data_Date-Issued])>=[Enter Start Date:]
AND [qry-WO].[WO-Data_Date-Issued])<=[Enter End Date:]))
GROUP BY [qry-WO].[S-Data_Store-Type]
PIVOT [qry-WO].[WO-Data_DWM-Contact];

It's the PARAMETERS declaration that does the trick.
 
Try
WHERE [qry-WO].[WO-Data_Date-Issued] Between [Enter First] and [Enter Last]

no '='
Also, for a crosstab query you must define the parameters (via Query,
Parameters dialog). You enter each parameter and the data type.

The SQL becomes..

PARAMETERS [Enter First] DateTime, [Enter Last] DateTime;
TRANSFORM Count...etc
 
Back
Top