Dynamically referencing crosstab columns

[Jeremy Maddrey]

I am currently working on a labels report that is based off of a
crosstab query. The crosstab query generates basic info (name,
address, etc.), plus a column named as a publication id. For example,
a publication "ESPN The Magazine" has an id of 8. The column in the
crosstab is named 8, and the # of publications ordered by each user is
the value in that column. How do I dynamically reference the columns
that are generated in the crosstab query? I need to print labels
every day, so obviously not every publication will be ordered within
that day and therefore, column 8 will not always be present, and can't
be hardcoded. Any ideas?

 

[Allen Browne]

Simplest approach is to set the Column Headings property of the crosstab
query so that all possible columns are nominated, even if they have no data.
A report that refers to this column will then work.

If the field names are numbers, include them in square brackets.

It is also possible to create a report and save it with the text boxes
unbound. Then in the report's Open event, you could loop through the Fields
collection of the query it is based on to learn the Name of each field, and
set the ControlSource of the report's controls appropriately. More messy
than the other approaches.