Parameter in a crosstab query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
Click to expand...

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
 
John Vinson said:
Care to post the SQL? How is the Quarter stored? Or is it calculated
from a date/time value?
Click to expand...

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");
 
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).
 
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.
Click to expand...

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
 
Back
Top