Automatcally change the dates in a report from a crosstab query

[Sherrie]

I am creating a report based on values in columns of the
last 4 months for clients. I am using it from a cross-tab
pivot table query with a formula to select the last 4
months values from todays date.

Is there anyway I can make my text boxes in the report
automattically change the dates instead of manually
changing them each month?

Thanks

 

[REVISED]

I tried using the link www.invisibleinc.com, but I can't
get get it to work for listing columns across i.e.
10/31/03, 9/30/03, 8/31/03, 7/31/03 with values underneath
it for each client.
Thanks
Sherrie

 

[Duane Hookom]

Hi Sherrie,
I'm not sure which sample from invisibleinc you used. I wrote these some
time ago.
I would definitely use relative months. You column headings expression would
be
ColHead: "Mth" & DateDiff("m",[DateField], Date())
Then set the Column Headings property to:
"Mth3", "Mth2", "Mth1", "Mth0"
Mth0 will be the current month and Mth3 will be 3 months earlier.

You can then build your report based on these four columns/fields. To get
column labels in your report, use text boxes with control sources of:
=DateAdd("m",-3,Date())
=DateAdd("m",-2,Date())
=DateAdd("m",-1,Date())
=DateAdd("m",-0,Date())
Format these to suit your needs.