Sharing parameters across queries via macro

litrelord

Well-known Member
Joined
Dec 1, 2002
Messages
519
Hi,

I have multiple tables with roughly the same information in each but each one for different clients.

At the moment there's a query set up for each of these which asks for start and end date parameters when run. I have a macro that runs each of these in turn and dumps them as an excel file onto my hard drive. What I'd like is to only have to enter the parameters in once and have this shared across all of the queries. How would I go about this?

The alternative would be to use an append query and then dump the output of that but I'm not having much luck with that so i'll save those problems for another question.

thanks

Nick
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

litrelord

Well-known Member
Joined
Dec 1, 2002
Messages
519
Trouble is the database is linked to other databases and the person who set them up is unlikely to want to change everyone else's just to make my life easier (or take kindly to me suggesting they've set it up inefficiently).

There isn't actually any duplicated data. There are fields duplicated across all of the clients tables but the fields aren't identical in every table. Some have more/less/different fields (they're questionnaire responses). All I need is to extract the data from each table for a specified period. Having them all as seperate files isn't a problem.

I was thinking maybe it's possible to have the parameter responses captured in a table and then link the criteria of the other queries to these table fields. Would that work?

Just typing as I think here so feel free to tell me that this would be a really terrible idea.
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi Nick

Create a form which you use to capture the start and end dates. Place two unbound text boxes on the form and give one the name 'StartDate' and the other 'EndDate'. Before you've added a button and got it to open / run your queries, change each query design by adding the following criteria under the relevant date field :

Between [Forms]![MyForm].[StartDate] And [Forms]![MyForm].[EndDate]

The queries will now look to the form for the criteria.

HTH, Andrew

P.S. make sure you use your actual form name where I used 'MyForm'.
 

litrelord

Well-known Member
Joined
Dec 1, 2002
Messages
519
Like all good solutions - just makes me kick myself for not thinking of it myself.

Perfect, thanks Andrew.
 

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top