Re: Cross-tab Criteria not evaluating

Here is a standard FAQ I have posted at Tek-Tips. You should be able to
change the solution to days rather than months.

Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. 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 with menuing: Query|Parameter
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:
"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.

Duane Hookom
MS Access MVP

"Juan Schwartz" <jimmy.kirk@xxxxxxxxx> wrote in message
>I did some searching around and found a post where you addressed that
> question. I have added it to the parameters and it seems to be working
> fine now with the exception of the report. I'm still very interested in
> how that would work. The set number is 7 for the columns.


Relevant Pages

  • Re: programmatically add controls to report
    ... > ' Add crosstab value to total for current column. ... >>create unique column headings based on the customer since ... >>> I have a pre-built report that runs off a query. ...
  • Re: Report casues crash
    ... Thanks for the info ans steps to solve my report crashing problem. ... a crosstab query gets its column headings from one of the fields. ... That means Access has to run the query before it knows what the fields are. ...
  • Re: Crosstab Report
    ... Thanks to you both Allen and Duane. ... Specify the column names in the query's Column Headings ... You can save the report without enough text boxes to cope with the ... Then in the Open event of the report, generate the crosstab query ...
  • Re: Crosstab Report
    ... Thanks for the information about dynamic column headings. ... some of the information you posted to the query and found it very useful. ... would like to choose the year the report starts. ... Then in the Open event of the report, generate the crosstab query ...
  • Re: Data on report based on current date
    ... my question is not how to create the column headings. ... Let's say the report was printed today; here's how the column headings would ... >> Column headings in the crosstab query are 1-12, ... >> expressions but I don't know what kind of expression to use. ...