Changing running order of queries in report

[Guest]

Hi,

I have a report with numerous queries in it. All of the queries require
start and end dates to be typed in.
At the moment the report asks for the dates in a pretty random order, is
there anyway that I can select which order the queries are run before
printing?

Thanks

 

[Allen Browne]

Within any one query, you can set the order of parameters by declaring them.
Choose Parameters on the Query menu (in query design.)

The order the queries will fire should be consistent (if cryptic.) If your
report contains subreports, then the subreports will fire their queries for
each record in the main report, so this could happen several times and
appear to be random (since it depends on which records in the main report
require them.)

One way around the issue would be to create a simple unbound form with a
couple of text boxes for entering the limiting dates. The main report's
query and the subreports' queries can all read the dates from the form so
you don't have to re-enter the same dates all the time.

1. Create a form with 2 text boxes named (say) txtStartDate and txtEndDate.
Set the Format property of these text boxes to "Short Date" or similar so
that only valid dates can be entered. Save the form as (say) frmReportDates.

2. Open the main report's query in design view. Replace your [StartDate]
parameter with:
[Forms].[frmReportDates].[txtStartDate]
and the [EndDate] with:
[Forms].[frmReportDates].[txtEndDate]
To ensure Access understands these text boxes as dates, choose Parameters on
the Query menu, and enter 2 rows in the dialog:
[Forms].[frmReportDates].[txtStartDate] Date/Time
[Forms].[frmReportDates].[txtEndDate] Date/Time
Save the query.

3. Repeat step 2 for any subreport queries that should read the same dates
from the same boxes.

Now, provided the form is open and the dates are entered, you should be able
to open the report without it asking for any more parameters.

 

[fredg]

Hi,

I have a report with numerous queries in it. All of the queries require
start and end dates to be typed in.
At the moment the report asks for the dates in a pretty random order, is
there anyway that I can select which order the queries are run before
printing?

Thanks

Are all of the Start Dates the same date? Are all of the End Dates the
same date?

Use a form with 2 controls.
StartDate and EndDate

Add a command button to the form.
Code it's Click event:
Me.Visible = False

Code each query's Date field criteria:
Between forms!FormName!StartDate and foemd!FormName!EndDate

Code the Report's Open event:
DoCmd.OpenForm "FormName", , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "FormName"

Open the report.
It will open the form.
Enter the Start and End dates.
Click the command button.
The report will display. When you close the report, it will close the
form.

 

[Guest]

Are all of the Start Dates the same date? Are all of the End Dates the
same date?

If you use identical prompts then you will be prompted only once for each.
I alway copy and then paste the prompt to make sure that there is no
differences.