User prompt between 2 dates used in multiple queries

[sean98]

I am running a query that pulls in data from three other queries. Each query pulls data from seperate tables. The tables represent data for each year. Each record in all tables contain a week number. I have prompted the user to input the week range they would like to pull data from (Between [Week 1] and [Week 2]). The query was built to pull comparable years data for the same weeks. Right now the user has to input the same weeks three times, one for each query. I would like the user to be able to input the data only once and have the other queries recognize these dates when running their information.

Any thoughts on how I can do this would be appreciated.

Cheers,
Sean

 

[NateO]

Hello Sean, not exactly following, but see if this is the ticket:

http://www.mrexcel.com/board2/viewtopic.php?t=53578

 

[sean98]

Thanks for the insight Nate but I managed to trip over the answer when I was playing around. I just made sure each query had the same input names. Using the example I noted each query needed to run using the date range between weeks 1 and weeks 2 thus I put the same formula in each query under the week field ... between [Week 1] and [Week 2]

I hope this make sense for whomever might be looking at this.

 

[Dugantrain]

Sean,
One way to do this would be to have a form with two text boxes on it. Name them something like "txtStartDate" and "txtEndDate". In the Where Clause of your queries, remove the parameters that you have put on the dates and in their place, write:

Between Forms!Your_Form_With_The_Textboxes.Form!TxtStartDate AND Forms!Your_Form_With_The_Textboxes.Form!TxtEndDate

This will ensure that you only need to fill in the dates on your form one time for each query that calls those text boxes. You can also do some cool stuff like have a command button on the form which will automatically run the queries when you press, validate that the text in the text boxes are valid dates, etc. etc. etc.