CrossTab quiry with Parameters

[Guest]

Hi, All
How can i use crosstab query and prompt user to enter parameters like:
The syntax is wrong there, is it possible at all?
TRANSFORM Sum(Master.Amount) AS SumOfAmount
SELECT Master.Shield, [ViolCd] & " " & [ViolDescrip] AS Viol
FROM Master
WHERE Master.IssDate=Between [Enter Start Date] AND [Enter End Date]
GROUP BY Master.Shield, [ViolCd] & " " & [ViolDescrip]
PIVOT IIf([ActCd]="10","Valid","Void");

 

[Jason Lepack]

Hi, All
How can i use crosstab query and prompt user to enter parameters like:
The syntax is wrong there, is it possible at all?
TRANSFORM Sum(Master.Amount) AS SumOfAmount
SELECT Master.Shield, [ViolCd] & " " & [ViolDescrip] AS Viol
FROM Master
WHERE Master.IssDate=Between [Enter Start Date] AND [Enter End Date]
GROUP BY Master.Shield, [ViolCd] & " " & [ViolDescrip]
PIVOT IIf([ActCd]="10","Valid","Void");

Remove the = in this statement:
WHERE Master.IssDate=Between [Enter Start Date] AND [Enter End Date]

Cheers,
Jason Lepack

 

[John Spencer]

In a crosstab you must declare the parameters in the query.

Parameters [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Sum(Master.Amount) AS SumOfAmount
SELECT Master.Shield
, [ViolCd] & " " & [ViolDescrip] AS Viol
FROM Master
WHERE Master.IssDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY Master.Shield, [ViolCd] & " " & [ViolDescrip]
PIVOT IIf([ActCd]="10","Valid","Void");

 

[John Vinson]

Hi, All
How can i use crosstab query and prompt user to enter parameters like:
The syntax is wrong there, is it possible at all?

It's always permissible to specify a query's Parameters - but with a
crosstab it is obligatory! Also, you can use the = operator *or* the
BETWEEN operator but not both in the same criterion. Try:

PARAMETERS [Enter Start Date] Date/Time, [Enter End Date] Date/Time;
TRANSFORM Sum(Master.Amount) AS SumOfAmount
SELECT Master.Shield, [ViolCd] & " " & [ViolDescrip] AS Viol
FROM Master
WHERE Master.IssDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY Master.Shield, [ViolCd] & " " & [ViolDescrip]
PIVOT IIf([ActCd]="10","Valid","Void");

John W. Vinson[MVP]

 

[Guest]

Thank you, all
This is exactly what i need, it works great!

Hi, All
How can i use crosstab query and prompt user to enter parameters like:
The syntax is wrong there, is it possible at all?

It's always permissible to specify a query's Parameters - but with a
crosstab it is obligatory! Also, you can use the = operator *or* the
BETWEEN operator but not both in the same criterion. Try:

PARAMETERS [Enter Start Date] Date/Time, [Enter End Date] Date/Time;
TRANSFORM Sum(Master.Amount) AS SumOfAmount
SELECT Master.Shield, [ViolCd] & " " & [ViolDescrip] AS Viol
FROM Master
WHERE Master.IssDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY Master.Shield, [ViolCd] & " " & [ViolDescrip]
PIVOT IIf([ActCd]="10","Valid","Void");

John W. Vinson[MVP]