Dynamic Column Headings Crosstab Report

natep26 · Sep 5, 2008

I created a crosstab report from a crosstab query using the example from the solutions9 database.

I have one little hang up. If I run the report, I recieve the error "Jet database engine does not recognize '' as a valid field name or expression."
From reading, it appears that the problem is fixed by declaring the column headers in the Column Headings Field of the Query Properties. If I do this, the query and report work great. However, I am looking for a way for the column headings to be created automatically.

Thanks for your help.

pbaldy · Sep 5, 2008

I use code from the Access Developer's Handbook, which I think it better, but there's also this:

http://support.microsoft.com/kb/328320/en-us

DCrake · Sep 8, 2008

Are you trying to ensure that missing headings are utilised in the column headings? Whereby if you are running a crosstab grouped by the month of a date and there is no data for a specific month and you still want that month to appear a blank column.

A cross tab query wil only give column heads for groupings that contain data. No data for July no July column.

Can you specify more?

natep26 · Sep 8, 2008

The headings are created by quarter. When I run the Report I want the column headings to be the same as the query depending on the date range I run the report for. In the query, everything comes up fine. For it to work in the report, I'd have to put the coumn headings into the query properties.

DCrake · Sep 8, 2008

Lets say in yourt query you are running the query for the full year to date effect but you are only half way through the year. You will get data for Q1 and Q2 and Total. But for presentation purposes you would like to show the Q3 and Q4 column headings. This is the point that you use the query column headings property to pre populate the col headings. As they are static for that year then this will be a once a year activity.

natep26 · Sep 8, 2008

In your example, I don't want to show the columns for the 3rd and 4th quarters if there is no data for the period. The problem is, if I don't put anything in the query column headings property, then I get the "Jet database engine does not recognize '' as a valid field name or expression." error.

DCrake · Sep 9, 2008

Is it possible you could supply a working example of the senerio you have?

Dynamic Column Headings Crosstab Report

natep26

Registered User.

pbaldy

Wino Moderator

DCrake

Remembered

natep26

Registered User.

DCrake

Remembered

natep26

Registered User.

DCrake

Remembered

Similar threads

Users who are viewing this thread