I use the same parameter in 2 queries - how do I only enter once?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that uses a subreport. Same company/same date range.
Currently each query asks for the company name and the beginning and ending
date so I have to enter each twice when I run the report.
Ideally, I would only have to enter this once.

How do I do this?

Thanks for your help,
Joe
 
Enter it in a control of a form? Even if the form is hidden, that is ok, as
long as it is open. Then, use the syntax FORMS!formNameHere!ControlNameHere
for each query.

Note that if the queries are cascaded (one inside the other) there is no
need to supply the parameter twice.


Hoping it may help,
Vanderghast, Access MVP
 
Use a form with a couple of unbound text boxes, and enter the the date
there. In the queries that feed your subreports, put something like this in
the Criteria row under the relevant field:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]

If desired, you can also add a command button to the form to preform the
OpenReport.
 
Thanks - that got me a good start. I used a combo box for the company, and
that part works great.

The error message I receive now is Type Mismatch in Expression error. I am
confused by this because the Source data is formatted as Short Date, and so
are the unbound text boxes I am using on the form. Any suggestions what may
be the issue?

Here is what I have entered in the query criteria:
Between [Forms]![ReportParametersF]![StartDate] And
[Forms]![ReportParametersF]![EndDate]

Thanks again,
Joe

Allen Browne said:
Use a form with a couple of unbound text boxes, and enter the the date
there. In the queries that feed your subreports, put something like this in
the Criteria row under the relevant field:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]

If desired, you can also add a command button to the form to preform the
OpenReport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Pogue said:
I have a report that uses a subreport. Same company/same date range.
Currently each query asks for the company name and the beginning and
ending
date so I have to enter each twice when I run the report.
Ideally, I would only have to enter this once.

How do I do this?

Thanks for your help,
Joe
Click to expand...
Click to expand...
 
Note: I should also mention the query is a make table query. The
destination table created is also formatted to be Short Date.


Pogue said:
Thanks - that got me a good start. I used a combo box for the company, and
that part works great.

The error message I receive now is Type Mismatch in Expression error. I am
confused by this because the Source data is formatted as Short Date, and so
are the unbound text boxes I am using on the form. Any suggestions what may
be the issue?

Here is what I have entered in the query criteria:
Between [Forms]![ReportParametersF]![StartDate] And
[Forms]![ReportParametersF]![EndDate]

Thanks again,
Joe

Allen Browne said:
Use a form with a couple of unbound text boxes, and enter the the date
there. In the queries that feed your subreports, put something like this in
the Criteria row under the relevant field:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]

If desired, you can also add a command button to the form to preform the
OpenReport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Pogue said:
I have a report that uses a subreport. Same company/same date range.
Currently each query asks for the company name and the beginning and
ending
date so I have to enter each twice when I run the report.
Ideally, I would only have to enter this once.

How do I do this?

Thanks for your help,
Joe
Click to expand...
Click to expand...
Click to expand...
 
Allen Browne said:
Use a form with a couple of unbound text boxes, and enter the the date
there. In the queries that feed your subreports, put something like this in
the Criteria row under the relevant field:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]

If desired, you can also add a command button to the form to preform the
OpenReport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Pogue said:
I have a report that uses a subreport. Same company/same date range.
Currently each query asks for the company name and the beginning and
ending
date so I have to enter each twice when I run the report.
Ideally, I would only have to enter this once.

How do I do this?

Thanks for your help,
Joe
Click to expand...
Click to expand...
 
Back
Top