Query parameter & Subreports

[Guest]

Very much a newbie here. I'm trying to generate a report with user defined
date range that has multiple subreports in it. I've got a query with
parameters set tied to my table with the information in it. Problem: When I
put one subreport in the main report, it works fine. The query asks for
dates. If I put more than 1 subreport in the main report, the date parameter
box pops up for each and every one.
Should the record source on each subreport point to the query?
How can I input date range once and have it apply to all subreports?
Please Help!!
Jeff F.

 

[fredg]

Very much a newbie here. I'm trying to generate a report with user defined
date range that has multiple subreports in it. I've got a query with
parameters set tied to my table with the information in it. Problem: When I
put one subreport in the main report, it works fine. The query asks for
dates. If I put more than 1 subreport in the main report, the date parameter
box pops up for each and every one.
Should the record source on each subreport point to the query?
How can I input date range once and have it apply to all subreports?
Please Help!!
Jeff F.

Use a form to enter the parameters in.
Close the form when the report closes.

Create an unbound form. Add 2 Text Controls.
Name one StartDate and the 2nd EndDate.

Add a command button.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In each query, in it's Date field's criteria line, write:
Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate

Next, code the main report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the main report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report. The form will display
and wait for the entry of the dates. Click the command button and the
report will run without need for any further parameter entries. When
the report closes, it will close the form.

 

[Guest]

Thanks Fred.
My command button was messing everything up for me.

On a similar note:
would it be easy to create a date range where the user could just press a
command button that said " Last week" and it would return data from last week
(monday thru sunday) regardless of what day it is today.
I am repeatedly printing reports for the date range of "last week" and this
would make it easy.
Thanks again for your quick response!

 

[John Vinson]

would it be easy to create a date range where the user could just press a
command button that said " Last week" and it would return data from last week
(monday thru sunday) regardless of what day it is today.

A criterion of

= DateAdd("d", - WeekDay(Date(), 2) - 8, Date()) AND < DateAdd("d", - Weekday(Date(), 2), Date())

will do that, I think - try it out.

John W. Vinson[MVP]

 

[Guest]

fredg,
Thanks for your help. Everything works fine except i have a cancel button on
the dialog box form that doesn't work properly when i open the report. It
closes the form but the parameters in the subreports still pop up instead of
closing the report and all. How do i stop the report from opening (using
acdialog to get data from dialog box)?