Report with parameters

[Guest]

Hi, All
I am using Access 2003.
I have a query:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM COUNT(Master.TicketNo) AS CountofTicks
SELECT [ViolCd] & " " & [ViolDescrip] AS Viol
FROM Master
WHERE Master.IssDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY [ViolCd] & " " & [ViolDescrip]
PIVOT IIf([ActCd]="10","Valid","Void");

I need to create report on this query, when i start with the report wizard,
i can select the query, but no fields available for the report... is it
possible because of the
parameters?
I want click on the report, enter parameters and view or print report,

Please, help

 

[John Spencer]

Try this modification

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM COUNT(Master.TicketNo) AS CountofTicks
SELECT [ViolCd] & " " & [ViolDescrip] AS Viol
FROM Master
WHERE Master.IssDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY [ViolCd] & " " & [ViolDescrip]
PIVOT IIf([ActCd]="10","Valid","Void") IN ("Valid","Void")

Forcing the column titles for the pivot will often take care of the problem,
since Access can detemine what all the possible fields will be. Otherwise,
Access will run the query to get the available field names.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Also, it is generally a much better idea to use controls on forms for
entering criteria. Parameter prompts lack too much functionality.

--
Duane Hookom
Microsoft Access MVP

Try this modification

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM COUNT(Master.TicketNo) AS CountofTicks
SELECT [ViolCd] & " " & [ViolDescrip] AS Viol
FROM Master
WHERE Master.IssDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY [ViolCd] & " " & [ViolDescrip]
PIVOT IIf([ActCd]="10","Valid","Void") IN ("Valid","Void")

Forcing the column titles for the pivot will often take care of the problem,
since Access can detemine what all the possible fields will be. Otherwise,
Access will run the query to get the available field names.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi, All
I am using Access 2003.
I have a query:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM COUNT(Master.TicketNo) AS CountofTicks
SELECT [ViolCd] & " " & [ViolDescrip] AS Viol
FROM Master
WHERE Master.IssDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY [ViolCd] & " " & [ViolDescrip]
PIVOT IIf([ActCd]="10","Valid","Void");

I need to create report on this query, when i start with the report
wizard,
i can select the query, but no fields available for the report... is it
possible because of the
parameters?
I want click on the report, enter parameters and view or print report,

Please, help

 

[Guest]

Thank you, it works great,
I didn't know yet how to use forms in Access, i need to learn more, but for
now
i have to build a few reports, mostly crosstab with parameters.
Thank you, again

Also, it is generally a much better idea to use controls on forms for
entering criteria. Parameter prompts lack too much functionality.

--
Duane Hookom
Microsoft Access MVP

Try this modification

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM COUNT(Master.TicketNo) AS CountofTicks
SELECT [ViolCd] & " " & [ViolDescrip] AS Viol
FROM Master
WHERE Master.IssDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY [ViolCd] & " " & [ViolDescrip]
PIVOT IIf([ActCd]="10","Valid","Void") IN ("Valid","Void")

Forcing the column titles for the pivot will often take care of the problem,
since Access can detemine what all the possible fields will be. Otherwise,
Access will run the query to get the available field names.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi, All
I am using Access 2003.
I have a query:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM COUNT(Master.TicketNo) AS CountofTicks
SELECT [ViolCd] & " " & [ViolDescrip] AS Viol
FROM Master
WHERE Master.IssDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY [ViolCd] & " " & [ViolDescrip]
PIVOT IIf([ActCd]="10","Valid","Void");

I need to create report on this query, when i start with the report
wizard,
i can select the query, but no fields available for the report... is it
possible because of the
parameters?
I want click on the report, enter parameters and view or print report,

Please, help