Need to set report column headers to qry value

[rfuscjr via AccessMonster.com]

I am unsure the best way to do this. I have a report that shows a rolling 15
months of data across the page thus the column headers need to change each
month. (I can not use the column names from the query because they are
generic, ie: Mth1, Mth2...Mth15) and the boss does not want to see Mth1 but
rather Jun07, Jul 07 etc etc.

I do not want to manually key these in each time the report is run; I want to
automate it. I thought of creating 15 queries, each returning a single value
(Jun07 etc) that represents the appropriate column's header value. I'd then
add 15 boxes as report headers and point each box to the appropriate query.
When I try to connect the box to a query in the control source (drill my way
to the query name) and open the form, '?Name' appears as opposed to the
result of the query it should be running.

Ideas?

 

[Allen Browne]

There are a couple of ways to handle this.

One approach is to design the fields so they don't change (1, 2, ...), and
then design the report so it shows the values you need in text boxes over
the columns.

The other is to save the report as unbound, and use the Open event procedure
of the report to loop through the controls (txt0, txt1, ... txt14) in a loop
assigning the ControlSource. This requires some ability to write VBA code,
and some ability to create an SQL statement.

 

[Duane Hookom]

There is a dynamic monthly crosstab reporting solution at
http://www.tek-tips.com/faqs.cfm?fid=5466.