Multiple queries changing criteria

[TClark14]

Here is what I would like to do. I don't know if this is possible or not, or how to do it.

I have a command button on a form on the "on click" that runs several queries at once. These queries already have criteria in them. But now I realize there are several other reports I can run using these same queries, except I need to change the criteria in each one (there are quite a few).

Is it possible to remove the crieria from the queries themselves and then be able to enter the criteria I need for all the queries when I click the command button.

For example I want all the queries to run based on this criteria:
between[Beginning Date]And[Ending Date]

For another report - I do not need the date criteria, but need to set the criteria for all the queries to Yes on a Yes/No field.

I am hoping I do not have to go in and change or copy all these queries each time I want to change the criteria I need.

Sorry for being so long-winded - I hope this makes sense.

Thanks
Terry

 

[jwindon]

You could have several command buttons that change the criteria of your query using code firing on the OnClick event of the command button.

OR....

You can go an easier route....several command buttons and several queries.

For the date query, the query grid could ask for input rather than be dependent on a set value.

 

[Jack Cowley]

One way to do your Begin-End date is to have a form with two unbound fields. One called BeginDate and the other EndDate. In your queries but this in the Criteria line for the date:

Between [Forms]![YourFromName]![BeginDate] and [Forms]![YourFromName]![EndDate]

Now this form will work for all your queries with a Begin and End date. Command buttons for each report or some other code should get you started.

 

[TClark14]

I am not sure if I explained what I needed correctly, or if I am not understanding what you mean.

But if I have a macro that will run 25 queries at once, and I want them all to run based on say the date range 10/01/01 to 10/31/01 (I know how to have the query ask me for the beginning and ending date to be entered), will it not come up and ask me to enter it 25 more times for each of the 25 queries?

What I hoped to be able to do was have it ask me for the criteria once and use that crieria for all 25 queries. And I would also like the choice to query on a different field in the query rather than the date field, if I want to run the same 25 queries for a different report.

I am confusing myself!

Thanks
Terry

 

[TClark14]

OOOPS - I think we both replied at the same time - My first reply was to JWINDON.

Terry

 

[jwindon]

Yes. It will ask you 25 times...but use Jack's method. I gave you the short answer.

If you implement the use of the unbound form, you will only have to enter the date parameters once. Have each of your 25 queries use the criteria from the unbound form controls. Add yet one more control to this unbound form...for the other field or fields you wish these queries to run on.


Utilize the OR criteria of the query along with the date parameter.

[This message has been edited by jwindon (edited 11-21-2001).]

 

[kplatt]

Hello, this posting was very helpful, although i do have one question. When you do not have the form open the query deafauls to ask for a begin date and end date. Is there a way to revise the expression to run in a way that when the form is not open or the fields are left blank, that all the data can be shown. I am asking because i need to be able to specify dates and be able to see the data when the form is not open or the begin and end date are left blank. Obviously I could put a beginning start date and some date in the future and get the same data but I was wondering if I could make the defualt show all data. thanks

 

[SOS]

I use a method like this one (see sample here) where I don't include criteria in the query but instead use the where clause to open a report or form based on the criteria given. I don't work in queries directly, but use a form for displaying the query results and that allows me to use that feature.

 

[highandwild]

why not have a table in which you store the dates and then attach the table unlinked to each of the queries and use the dates for the criteria.

 

[kplatt]

Thanks for your input guys, Below is what i put in the query and referenced it to an unbound form.

Between [Forms]![a]![BeginDate] And [Forms]![a]![EndDate]

This works and I am ok with it, but i also have the need to see all data after I report for 2009. When the referenced form is not open or the fields in the form are left blank, the criteria remains in the query and asks for the criteria when i try to run each individual query. Is there a way to show all data that would typically show in the query without a criterial as a default if the unbound form was not open for or if the unbound form fields are left blank. Is there a " show all" iff function or something?

Thanks