Dynamic Monthly
|
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:
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: =DateAdd("m",0,Forms!frmA!txtEndDate) =DateAdd("m",-1,Forms!frmA!txtEndDate) =DateAdd("m",-2,Forms!frmA!txtEndDate) =DateAdd("m",-3,Forms!frmA!txtEndDate) ... < This solution requires no code and will run fairly quickly. |
|