Multiple queries in one report

[Ryan W]

I have three queries with date range criteria. I would like to put all three
queries into one report. When I do this, the criteria box asks me three time
what my dates are. Is there any way that I can only have the report ask me
once to get the information that I am looking for? Thanks in advance, Ryan

 

[Douglas J. Steele]

Easiest way would be to create a form that you key the date into (or use a
Calendar control for more user friendliness) and base each of the queries on
that control.

 

[Ryan W]

Can you please explain in more detail.

Easiest way would be to create a form that you key the date into (or use a
Calendar control for more user friendliness) and base each of the queries on
that control.

 

[Douglas J. Steele]

Presumably you've got something like BETWEEN [Start Date] AND [End Date] in
your queries.

Create a form (let's call it frmQuery) with text boxes on it (let's call
them txtStart and txtEnd). Change your WHERE clause to BETWEEN
Forms!frmQuery!txtStart AND Forms!frmQuery!txtEnd.

As long as frmQuery is open when you run the queries, it'll pick up whatever
values are in those two text boxes and use them in the queries.

As I mentioned, you can get fancier and use Calendars rather than text
boxes, but the principle's the same: put references to your calendar fields
in the query, rather than other parameters.

 

[Ryan W]

Doug,
Thank you for the advice. It makes sense to me and I will attempt tonight to
create this form. I appreciate your help, and I do like your website...keep
brewing! Thanks, Ryan

 

[Ryan W]

Doug,
I followed your advice. I made a form with the txtstart and txtend and kept
the form open. I switched the query to read "BETWEEN Forms!frmQuery!txtStart
AND Forms!frmQuery!txtEnd". When I create my report, it still asks me for
days and then entries for the two text boxes. I am not sure what I am doing
wroing, but can you please help.

I have three queries with Between [start_date] And [end_date] and I am
trying to combine them into one report. When I do this, I am asked three
times for the start/end dates. Is there a way that I can be asked once and
then have the information displayed from all three reports for that time
period? Again, thank you for your assistance, I do appreciate your time.

Ryan

Presumably you've got something like BETWEEN [Start Date] AND [End Date] in
your queries.

Create a form (let's call it frmQuery) with text boxes on it (let's call
them txtStart and txtEnd). Change your WHERE clause to BETWEEN
Forms!frmQuery!txtStart AND Forms!frmQuery!txtEnd.

As long as frmQuery is open when you run the queries, it'll pick up whatever
values are in those two text boxes and use them in the queries.

As I mentioned, you can get fancier and use Calendars rather than text
boxes, but the principle's the same: put references to your calendar fields
in the query, rather than other parameters.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Can you please explain in more detail.

 

[Ryan W]

Doug,
I was able to create my frmquery with my textboxes. I changed my criteria in
my query to reflect the txtboxes on my form. When I run my query though, it
does not return any results. What am I doing wrong? Thanks, Ryan

Presumably you've got something like BETWEEN [Start Date] AND [End Date] in
your queries.

Create a form (let's call it frmQuery) with text boxes on it (let's call
them txtStart and txtEnd). Change your WHERE clause to BETWEEN
Forms!frmQuery!txtStart AND Forms!frmQuery!txtEnd.

As long as frmQuery is open when you run the queries, it'll pick up whatever
values are in those two text boxes and use them in the queries.

As I mentioned, you can get fancier and use Calendars rather than text
boxes, but the principle's the same: put references to your calendar fields
in the query, rather than other parameters.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Can you please explain in more detail.