[AccessD] Dynamic Report Based on Crosstab Query

Hi Myke

You may be able to simplify that code by letting the query return
fixed column names like 0, 1, 2, .. to indicate the difference in
years from the chosen year, then adjusting on the opening of the
report the caption of those labels according to the chosen year.

Something like (off my head) assuming the labels are named lbl0 to
lbl9:

  For intN = 0 to 9
    Me("lbl" & CStr(intN)).Caption = "FY " & Cstr(intYear + intN)
  Next intN

/gustav

> I have developed an app that uses reports based on crosstab queries.
> Each report looks like a spreadsheet, with a 10-year range of FYs in
> columns across the top. The first column (the "Base" year) has a label
> in the header called 'FY 2003', a textbox in the detail section with a
> control source of '2003', and a total textbox in the footer with a
> control source of ' =sum([2003]). Each column shows the next FY up to
> '2012'.

> The client wants to be able select the Base year -- for example, 'FY
> 2004', and have the report dynamically show columns FY 2004 - FY 2013.
> Before I spend several hours writing some code to change the columns in
> the report open event, I want to know if anyone has a better solution.