Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-05-2008, 12:54 PM   #1
natep26
Newly Registered User
 
Join Date: Oct 2007
Posts: 63
Thanks: 0
Thanked 0 Times in 0 Posts
natep26 is on a distinguished road
Dynamic Column Headings Crosstab Report

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.

natep26 is offline   Reply With Quote
Old 09-05-2008, 01:15 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,467
Thanks: 9
Thanked 3,760 Times in 3,703 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Dynamic Column Headings Crosstab Report

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
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-08-2008, 03:17 AM   #3
DCrake
Administrator
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 312 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: Dynamic Column Headings Crosstab Report

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?

__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Old 09-08-2008, 04:37 AM   #4
natep26
Newly Registered User
 
Join Date: Oct 2007
Posts: 63
Thanks: 0
Thanked 0 Times in 0 Posts
natep26 is on a distinguished road
Re: Dynamic Column Headings Crosstab Report

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.
natep26 is offline   Reply With Quote
Old 09-08-2008, 06:41 AM   #5
DCrake
Administrator
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 312 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: Dynamic Column Headings Crosstab Report

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.
__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Old 09-08-2008, 07:35 AM   #6
natep26
Newly Registered User
 
Join Date: Oct 2007
Posts: 63
Thanks: 0
Thanked 0 Times in 0 Posts
natep26 is on a distinguished road
Re: Dynamic Column Headings Crosstab Report

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.
natep26 is offline   Reply With Quote
Old 09-08-2008, 11:44 PM   #7
DCrake
Administrator
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 312 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: Dynamic Column Headings Crosstab Report

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


__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
'dynamic' cross tab report JimmyS Reports 5 05-19-2004 02:49 AM
Combo Box Preview Report Mike Hughes Reports 10 03-10-2004 10:04 AM
dynamic column headings theprez Reports 2 09-09-2003 08:48 AM
Group headings in multiple column report AChir Reports 1 03-18-2002 02:21 AM
[SOLVED] Date Format in Column Headings D B Lawson Queries 2 08-25-2001 12:41 AM




All times are GMT -8. The time now is 03:21 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World