create a report based on Xtab query with parameter

  • Thread starter Thread starter stella
  • Start date Start date
S

stella

Hi all!

I have a crosstab query with dates as columns. I made a
parameter where users enter the month and year and the
query will only show dates/columns that fall within that
month. How do I create a report based on this query? The
wizard won't allow me to choose the fields in this query,
it comes out blank. Without the parameter, the report
will include all the dates that are in the table and not
limit it to a particular month. Is it possible that I
create the parameter in the report, instead of in the
query? If so, how do I this, and is this the actual
solution?

Help, anyone?

THANK YOU SO MUCH!
 
Stella, this is not a trivial task, at least not usually. Chapter 9
of the Access 2K Developers Handbook (Volumn 1) (Getz, Litwin, and
Gilbert) contains a detailed description of how to accomplish this,
and the CD that comes with it actually has the code. If you don't
already have this resource, I'd strongly recommend getting it or the
version associated with whatever version of Access you are running.

As long as you only use the Day() value as your column headings and
not the actual date, and setup the pivot table to return days (1-31 no
matter what the month, check out the ColumnHeadings property of the
Crosstab query), the report you should be able to do this relatively
simply.

As a starting point, create a new query that uses your pivot table
query as it's source. Make this a make table query, so that it
creates a new table in your database that contains all of the
associated fields. Now use that to build your report. Once you have
the report designed using this table, change the reports datasource to
the pivot table query.

Good luck.

--
HTH

Dale Fye


Hi all!

I have a crosstab query with dates as columns. I made a
parameter where users enter the month and year and the
query will only show dates/columns that fall within that
month. How do I create a report based on this query? The
wizard won't allow me to choose the fields in this query,
it comes out blank. Without the parameter, the report
will include all the dates that are in the table and not
limit it to a particular month. Is it possible that I
create the parameter in the report, instead of in the
query? If so, how do I this, and is this the actual
solution?

Help, anyone?

THANK YOU SO MUCH!
 
In keeping with Dale's suggestion, set the column headings to:
ColHead: "Day" & Day([DateField])
Then set the column headings property to
"Day1","Day2","Day3",,..."Day31"
Make sure you set the criteria of your query to limit the results to one
month.
You can create a report directly based on this query.
 
Back
Top