Date Criteria in Report

[Amber]

I have built a main report that contains multiple
subreports. The subreports are based on queries that
contain date criteria, >=[Log Date Begin] And <=[Log Date
End. The problem is when I go to run my main report, and
I enter the begin & end dates, I have to keep repeating
the start and end dates....therefore my main report does
not run because I can't get past the start/end date
prompts.
I am not able to build a form to enter the start/end
dates because I have multiple tables that information
comes from.
How can I fix the repeated start/end date prompts in my
main report, so I just have 1 prompt for the entire
report?

 

[Peter R. Fletcher]

See my in-line responses,

I have built a main report that contains multiple
subreports. The subreports are based on queries that
contain date criteria, >=[Log Date Begin] And <=[Log Date
End. The problem is when I go to run my main report, and
I enter the begin & end dates, I have to keep repeating
the start and end dates....therefore my main report does
not run because I can't get past the start/end date
prompts.

You should use a Form to enter the begin & end dates.

I am not able to build a form to enter the start/end
dates because I have multiple tables that information
comes from.

This does not matter, since you will use a separate, _unbound_, form
popped up as a Dialog Box, hidden while the Report runs, and closed by
the Report's OnClose Event.

How can I fix the repeated start/end date prompts in my
main report, so I just have 1 prompt for the entire
report?

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[fredg]

I have built a main report that contains multiple
subreports. The subreports are based on queries that
contain date criteria, >=[Log Date Begin] And <=[Log Date
End. The problem is when I go to run my main report, and
I enter the begin & end dates, I have to keep repeating
the start and end dates....therefore my main report does
not run because I can't get past the start/end date
prompts.
I am not able to build a form to enter the start/end
dates because I have multiple tables that information
comes from.
How can I fix the repeated start/end date prompts in my
main report, so I just have 1 prompt for the entire
report?

Create an unbound form. Add 2 Text Controls.
Name one StartDate and the second one 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.