Column headings in a crosstab query

[Mike]

My database consits of every project my company has done from 1990 to today.
I want to prepare a report to show the money spent under a specific budget
line and fiscal year

I have created a crosstab query that has the following looks like the
following:

Fiscal Year
Budget Line Total Budget

When I run the query everything works fine for example i pasted it below:

Budget Line 2007 2008
WP-112 $7,500,000.00 $1,500,000.00
WP-112 TOC $18,500,000.00 $5,000,000.00

Using this query I want to prepare a report

However, I want to be able to use a form to input variable that will allow
me to specify which the years that will be used

For example, I may need a report that shows me from 2000 to 2009 or I may
need a report that shows me 2006 to 2007

Is there a way to enter a minimum value (ie 2000) and a maximum value (ie
2009) so that the column headers change and my report will automatically
change to

 

[Jerry Whittle]

Sure but there are a couple of problems to watch out for.

First you need a query just to pull in the records that you want. In the
Fiscal Year field, put something like the following in the criteria:

Between [Forms]![frmParameter]![txtStartFY] and
[Forms]![frmParameter]![txtEndFY]

For this to work you need a form named frmParameter with txtStartFY and
txtEndFY text box controls on it. The form must stay open, but can be
invisible, while the query is running.

In the query you need to go to Query, Parameters and set the data type for
both txtStartFY and txtEndFY.

Then you build your crosstab from the above query. Now here is the real
problem: You can't easily build a report based on a crosstab where the
columns can change. For example you have a field on the report looking for
2007; however, you didn't ask for that in the query. You will get an error.
Adding in all the years would clutter up things.

There are ways with code to dynamically create the fields in a report but
it's a lot of work.

 

[Mike]

Is there anyway you could show me how to do this. I got the reports all set
up the way i want them. All I need is the code to change the field names in
the report

Sure but there are a couple of problems to watch out for.

First you need a query just to pull in the records that you want. In the
Fiscal Year field, put something like the following in the criteria:

Between [Forms]![frmParameter]![txtStartFY] and
[Forms]![frmParameter]![txtEndFY]

For this to work you need a form named frmParameter with txtStartFY and
txtEndFY text box controls on it. The form must stay open, but can be
invisible, while the query is running.

In the query you need to go to Query, Parameters and set the data type for
both txtStartFY and txtEndFY.

Then you build your crosstab from the above query. Now here is the real
problem: You can't easily build a report based on a crosstab where the
columns can change. For example you have a field on the report looking for
2007; however, you didn't ask for that in the query. You will get an error.
Adding in all the years would clutter up things.

There are ways with code to dynamically create the fields in a report but
it's a lot of work.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

My database consits of every project my company has done from 1990 to today.
I want to prepare a report to show the money spent under a specific budget
line and fiscal year

I have created a crosstab query that has the following looks like the
following:

Fiscal Year
Budget Line Total Budget

When I run the query everything works fine for example i pasted it below:

Budget Line 2007 2008
WP-112 $7,500,000.00 $1,500,000.00
WP-112 TOC $18,500,000.00 $5,000,000.00

Using this query I want to prepare a report

However, I want to be able to use a form to input variable that will allow
me to specify which the years that will be used

For example, I may need a report that shows me from 2000 to 2009 or I may
need a report that shows me 2006 to 2007

Is there a way to enter a minimum value (ie 2000) and a maximum value (ie
2009) so that the column headers change and my report will automatically
change to