Dynamically changing text boxes and labels based on a Crosstab Query

[Denis Bisson]

Dynamically changing text boxes and labels based on a
Crosstab Query

I have a crosstab query which gives you the number of
audits performed by auditors for each of the past 5 years
(WHERE (((DateDiff("yyyy",[FromDate],Now()))<5)). The
datasheet view will include the past 5 years, where each
year will be a separate column (i.e. 2001, 2002, 2003,
2004, 2005).

The query works great. The associated report, however, is
a different story.

The report was created using a Wizard, in a previous year.
As a result, this year, instead of seeing the data for
years: 2001, 2002, 2003, 2004, 2005, the report shows
years: 2000, 2001, 2002, 2003, 2004. Of course, I have an
error msg, when running the report stating it doesn't
recognize the field [2000] - which is understandable, as
the query no longer recognizes it.

In the interim, I have gone into the report and changed
the ControlSources of each text box so that they may
display 2001, 2002, 2003, 2004, 2005. I have also had to
change the associated labels accordingly.

How do I get the text boxes and associated labels to
change dynamically based on the results of the query?

Thank you

Denis

 

[Marshall Barton]

Dynamically changing text boxes and labels based on a
Crosstab Query

I have a crosstab query which gives you the number of
audits performed by auditors for each of the past 5 years
(WHERE (((DateDiff("yyyy",[FromDate],Now()))<5)). The
datasheet view will include the past 5 years, where each
year will be a separate column (i.e. 2001, 2002, 2003,
2004, 2005).

The query works great. The associated report, however, is
a different story.

The report was created using a Wizard, in a previous year.
As a result, this year, instead of seeing the data for
years: 2001, 2002, 2003, 2004, 2005, the report shows
years: 2000, 2001, 2002, 2003, 2004. Of course, I have an
error msg, when running the report stating it doesn't
recognize the field [2000] - which is understandable, as
the query no longer recognizes it.

In the interim, I have gone into the report and changed
the ControlSources of each text box so that they may
display 2001, 2002, 2003, 2004, 2005. I have also had to
change the associated labels accordingly.

How do I get the text boxes and associated labels to
change dynamically based on the results of the query?

That would be a pain. Fortunately, there's an easier way.

Change the query's PIVOT clause to use:
"Year" & DateDiff("yyyy",[FromDate],Date())

Then you can bind the text boxes to the field names Year0,
Year1, . . .

The header labels would need to be changed to text boxes
with expressions like:
=Year(Date()) + 0
=Year(Date()) + 1
. . .

 

[Denis Bisson]

Thank you Marsh, It would have taken me quite a while to
figure this out on my own. It works great!

Cheers
Denis