Hook'D on Access


Dynamic Monthly
Crosstab Report

Try not to use "absolute" column headings for dates. You could possibly use "relative" dates.
This tip shows how to create monthly headings but will work just as well with other date intervals such as weeks, days, years, or other. For instance to change to weeks, change the DateDiff() and DateAdd() functions to use "ww" rather than "m".

For instance:
  • Form: frmA
  • Text Box: txtEndDate
  • Table: tblSales
  • Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the query parameters by menuing: Query|Parameters and enter:
    Forms!frmA!txtEndDate     Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)
This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
Column Headings: "Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months.

If you need column labels in your report, use text boxes with control sources of:

This solution requires no code and will run fairly quickly.
| Contact | ©2007 Duane Hookom