Automatcally change the dates in a report from a crosstab query

  • Thread starter Thread starter Sherrie
  • Start date Start date
S

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
 
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
 
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.
 
Back
Top