Parameter in a crosstab query

[Guest]

Hello,
I have a crosstab query that displays data by quarter. I would like a way
to display just the info for one quarter. I've tried creating a parameter,
but I must be missing something. I have tried to set the data type in the
parameter box, but then it displays all four quarters with the specified
quarter's info. Any ideas?
Thanks,
Melinda

 

[John Vinson]

Hello,
I have a crosstab query that displays data by quarter. I would like a way
to display just the info for one quarter. I've tried creating a parameter,
but I must be missing something. I have tried to set the data type in the
parameter box, but then it displays all four quarters with the specified
quarter's info. Any ideas?
Thanks,
Melinda

Care to post the SQL? How is the Quarter stored? Or is it calculated
from a date/time value?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Care to post the SQL? How is the Quarter stored? Or is it calculated
from a date/time value?

Here's the SQL. I've been trying to create my parameter from the "normal"
Access view (maybe that's my mistake). And the Quarter is calculated by the
DatePart Function from a Date value.

TRANSFORM Sum(tblMaintRep.[Cost of Repair]) AS [SumOfCost of Repair]
SELECT tblMaintRep.Vehicle, Sum(tblMaintRep.[Cost of Repair]) AS [Total Of
Cost of Repair]
FROM tblMaintRep
WHERE (((DatePart("yyyy",[DateR]))=DatePart("yyyy",Date()))
GROUP BY tblMaintRep.Vehicle
PIVOT "Qtr " & Format([DateR],"q");

 

[PC Datasheet]

For a cross-tab query, the ctiteria must be declared explicitly. Right click
in the grey background of query window and select Parameters from the menu
that appears. Add the Forms!... reference (copy
and paste it from the criteria line, it must match precisely) and specify
the datatype (date, number, whatever).

 

[John Vinson]

Here's the SQL. I've been trying to create my parameter from the "normal"
Access view (maybe that's my mistake). And the Quarter is calculated by the
DatePart Function from a Date value.

You need to explicitly specify the parameter:

PARAMETERS [Enter Quarter:] AS Integer;
TRANSFORM Sum(tblMaintRep.[Cost of Repair]) AS [SumOfCost of Repair]
SELECT tblMaintRep.Vehicle, Sum(tblMaintRep.[Cost of Repair]) AS
[Total Of
Cost of Repair]
FROM tblMaintRep
WHERE (((DatePart("yyyy",[DateR]))=DatePart("yyyy",Date()))
AND DatePart("q", [DateR] = [Enter Quarter:]
GROUP BY tblMaintRep.Vehicle
PIVOT "Qtr " & Format([DateR],"q");

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps