As explained in my previous post
To be dynamic and appear in a report is a different matter. If you can pivot on a value which is common to all requests - e.g. Day1, Day2 etc then that is fairly straightforward. However if you need dates 1/1/2014, 2/1/2014 etc, you would need to have in your report controls named say Day1, Day2 etc and some code to assign each of the pivoted column names to these control controlsources
To be able to use a crosstab as a form or report recordsource, you need to specify the column headings - to do this, open the crosstab and display the propertysheet for the top half of the query form as here
However the problem you have is that you don't know until the date range is specified by the user is what those column headings need to be, so you'll need some code to modify and save the crosstab query before opening the report.
At which point you have the next problem, which is that the controlsource for the controls relating to these columns also need to change and perhaps some hidden if the number of controls exceed the number of columns.
Which is the third issue - how many controls are you going to have? - what if the user only wants to see a few columns one day, then a whole year, the next.
These can be overcome, but you need to be clear about the rules for the user - perhaps there is a maximum number of columns to display - the maximum width of a form is 22 inches, so there is a 'natural' limit dependant on the width of the control in any event.
You need to decide a naming convention for the fields and controls - one that typically increments by 1 - e.g. Day1, Day2 to make automation of the process easier but is easily translateable back to the original value if required (for the header labels captions) - a simple trick I use is require the user to select a year and month, then the report has a maximum of 31 columns, numbered 1-31, or they select a year and the see 12 columns, one for each month - the others being hidden. Or you can do weeks - 52/53 columns
As said, this is doable, but decide what you want before determining the solution.
You may want to investigate using a pivot form before going down the crosstab route - initially base it on your first query, but remove the parameters looking at frmStats. There isn't a pivot report, but you can add a pivot form as a subform to a report
