During the creation of report, crosstab query fields are not seen

[DTO.]

Dear Friends,




I have created a crosstab query indicating a criteria like below;
between [Forms]![Formname]![firstdate] and [Forms]![Formname]![lastdate]

and then I have defined the parameters in parameter section at ribbon.
And over the form when I enter the parameters it gives me the query result correctly.


I want to create the report considering this crosstabquery I created. Problem is starting here. The field of the crosstab query is not seen below window during creation of the report.
Where I am doing wrong? What is the solution? Many thanks in advance.


And additional two notes:

normal query fields are seen during creation of report. But crosstab fields are not seen, this is the problem I trying to overcome.
If I remove the criteria from the crosstab query, report is seeing the fields of crosstab queryfields this time. So the problem should be related the setting criteria.

[CJ_London]

normal query fields are seen during creation of report. But crosstab fields are not seen, this is the problem I trying to overcome.

that's because the crosstab field names are not known until the query is run.

To overcome this, in the crosstab query properties, specify the names of the columns you want to appear in the column heading property, separated by comma's. For months it might be

Jan, Feb, Mar, etc


where Jan, Feb, Mar are the values returned in your crosstab column heading field

[DTO.]

Hello Ajax,

thank you very much for your answer. It works. And now report wizard is seeing the fields. But I have another issue. Let's continue from your example. There are 12 months I wroted in column heading in property section. And in the parameters, for example user choose from march to june. At this time, When the query run it shows 12 months completely although they were not choosen one. How can we skip the columns which is not choosen?

[CJ_London]

you will need to use vba to hide the columns not chosen. You may also need the vba to move the visible columns across. Alternatively, provide a 'standard' enumerated column name (such as mth1, mth2, etc) and use vba to assign the correct month to each header label and bind the detail control to the appropriate value. Plenty of examples on this and other access forums - this link for example https://www.accessforums.net/showthread.php?t=54565. Just search for 'access dynamic crosstab report'

What works for you very much depends on what your query actually is and how it is actually being displayed in the report