Use label captions to get query field data

[robert.feutl]

Hi,

I have a xtab query with field names in it that change depending on
criteria.

For instance, currently one field is "Field1". The field name is
printed on the report as a header.

As the field names change constantly in the query, I can not put fixed
name fields on the report. I would like to use the label caption to
retrieve the contents of the query field into a text field on the
report.

I tried using in the data source of the text field:
="[" & label.caption & "]"
but it did not work.

Any ideas to call the label caption in a text box to get the contents
of that field from the query?

 

[Allen Browne]

Dynamic field names are a problem for reports. The answer is not simple, so
you will need to be familiar with VBA code and SQL strings to achieve this.

One solution is to put enough text boxes into your report to meet the
demands of the maximum number of fields the query could generate. Leave them
unbound, and name them Text0, Text1, Text2, ...

Then in the Open event procedure of the report:
1. Retrieve a list of the values the column names.
2. Assign them to the Control Source of the text boxes.
3. Assign the Caption of each label above the controls.
4. Hide the unused text boxes and labels.
5. Set the Left and Width of each text box and label to make best use of the
available width of the report.

Unfortunately, a crosstab has to run to completion before all the column
names are known. If the query is complex or has lots of data, running the
query again in Report_Open to get the field names will mean the report takes
a long time to open. You may be able to work around that by using
OpenRecordset() on a query string that gets you all the possible values for
the column headings, and use that. In this case, you might also want to
assign a SQL statement to the report's RecordSource so you can include the
PIVOT clause (the Column Headings property of the crosstab).

 

[robert.feutl]

Allen,

I already have the column names pulled through a SQL statement and
placed on the report as label captions for headers.

The problem is with #2 of your suggestions: I can't seem to assign them
to the control source of the text boxes.

I was trying to use a function I created in the data source to pull the
data in from the query using the label caption, but I'm having trouble
with the correct wording to achieve #2.

I've tried every variation but it doesn't seem to work.

Any ideas?

 

[Duane Hookom]

You can pull all the column headings from your crosstab by saving a copy as
a standard totals query the removes the Row Heading columns and only groups
by your column headings. Use this query of each unique column heading value
as the record source of a multi-column subreport. This will display the
values across your report page.