Multiple Queries with same criteria

[Guest]

I'm teaching myself to use macros and VB so please assist me with this
question. I have multiple queries that I want to set up a macro to run and
export to excel. I can do that. For all of the queries the only criteria
that changes is the date range and for each run the range is the same for
all. Is it possible to set the dates as a variable and apply it to each
query in turn? Or is there a better way to acheive this that I'm not
realizing? Thank you in advance for your assistance. FYI I'm using Access
2003.

 

[Guest]

Put the criteria on a form, and reference the controls in the query.

Where somedate between [Forms]![formname]![controlname1] And
[Forms]![formname]![controlname2]

 

[Guest]

Steve,
Thank you for your quick response. However it doesn't seem to be
accomplishing the goal of only having to input the date range once and then
having that date range apply to multiple queries. It is possible that I am
not correctly setting up my form. It was my understanding that a form was a
set up to facilitate the enter/sorting of the data. It is possible my lack
of knowledge is coming into play here.
In my mind, I'm seeing an input box at the beginning of the macro which
will define the beginning and ending of the date range as variables. Then as
each query is opened having these variables applied as the beginning and
ending date ranges. Not sure if I'm missing a step that I should really
already know or not. Thank you again for your assistance.

Brian

Put the criteria on a form, and reference the controls in the query.

Where somedate between [Forms]![formname]![controlname1] And
[Forms]![formname]![controlname2]

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

I'm teaching myself to use macros and VB so please assist me with this
question. I have multiple queries that I want to set up a macro to run and
export to excel. I can do that. For all of the queries the only criteria
that changes is the date range and for each run the range is the same for
all. Is it possible to set the dates as a variable and apply it to each
query in turn? Or is there a better way to acheive this that I'm not
realizing? Thank you in advance for your assistance. FYI I'm using Access
2003.

 

[Steve Schapel]

Brian,

Entry/editing of data is indeed one of the key functions of forms in
Access. At other times, forms are used for other purposes as well. For
example, to provide an interface for navigation within the application.
And for another example, for the provision of selection criteria.

It is this last idea that Steve Clark was referring to. You need a form
which will be open at the time that you run your queries. And on that
form, you need a couple of unbound textboxes, for the entry of the
beginning and ending dates. So you enter the date criteria into these
textboxes *before* you run your queries.

And then, in the design view of the queries, in the Criteria row of the
date field, you need to reference these textboxes, using the type of
syntax suggested by Steve. I guess at the moment, you have something
entered there which causes you to be prompted to enter the dates as the
query runs. This is known as a parameter query. As far as I know, they
are never a good idea. So just replace whatever you have there now,
with the equivalent of:
Between [Forms]![NameOfYourForm]![NameOfBeginningDateTextbox] And
[Forms]![NameOfYourForm]![NameOfEndingDateTextbox]

 

[Guest]

Steve & Steve,
Thank you very much for your assistance. I have gotten it to work as I
had imagined. The issue was my unfamiliarity with forms. Thank you again
for your assistance.

Brian