Crosstab queries and reports

[Chi]

Hi,

I have the crosstab working by putting parameters in the crosstab -
Beginning Date and Ending Date.

It works fine when I run the query. However, I have a problem in creating a
report based on my crosstab query. There are no fields for me to choose from
the wizard.

Please help

Thanks
Chi

 

[Allen Browne]

Are the dates the column headings? If so, the actual field names generated
in the query will change depending on the parameters you enter. And since
the field names change, any report you design now won't have the right field
names for the future.

One way to solve this is to redesign the crosstab so it does produce
consistent column names. Duane Hookom shows how to do that in this example:
http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm

If you can't do that, other options include using the Column Headings
property of the query so all the field names are generated for the report
regardless of the parameters. Specifying the Column Headings will solve the
problem of making the field names available quickly in design view as well.
Details:
http://allenbrowne.com/ser-67.html#ColHead

But that may generate too many columns. You may therefore need to use VBA
code in the report's Open event to determine the field names, assign its
RecordSource, assign the ControlSource of the controls to the needed field
names, and set the captions over the columns. This will require an
understanding of VBA and SQL, so check out if Duane's solution is suitable
first.