Passing parameters from a report to it's underlying query

[tombergevin]

I'm trying to create a report that uses the same set of queries multiple times. I'm trying to build a daily, week to date, and month to date summary report.

The queries are crosstab queries with start and end date parameters defined. I have created a subreport that formats the data from these crosstab queries the way I want. I created a master report and use the subreport 3 times. I have not been able to figure out how to pass the required start and end dates to each interation of the report.

Database is Access 2000.

Any help would be greatly appreciated.

 

[padinka]

Here's an idea on how to do it the way you are trying to do it(without vba). In the query, first remove the parameters then put the following criteria in the start date field (I don't see why you need an end date):

>=Format(DateAdd("m", -1, DateAdd("d", (-Day(Now) + 1), Now())), "mm/dd/yy&quot




Then make 3 subreports instead of one. One for daily, one for weekly and one for monthly.

Do nothing on the monthly subreport except use the query as the data source.

For the daily subreport the data source for the subreport would be the same query (yep, with the monthly criteria) but would have the following criteria in the start date on the REPORT level criteria.

>=Format(now(), "mm/dd/yy&quot


For the Weekly subreport the record source for the subreport would have the following criteria in the start date on the REPORT level criteria depending on when your week starts.

SUNDAY START DATE

>=Format(DateAdd("d", (-Weekday(Now)+1), Now()), "mm/dd/yy&quot

MONDAY START DATE
>=Format(DateAdd("d", (-Weekday(Now)+2), Now()), "mm/dd/yy&quot



I haven't tried this for this purpose so try it at your own risk but since no one has answered...

Trisha
padinka@yahoo.com