Sharing parameters across queries via macro

[litrelord]

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

 

[Tanis]

Doesn't help your situation, but the data should be in normalized tables. This would prevent the problem you are experiencing. This means your data is not duplicated across many tables. Have a look here for normalization.

http://search.support.microsoft.com...20&ast=1&ast=2&ast=3&ast=4&ast=7&mode=a&adv=1

 

[litrelord]

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]

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]

Like all good solutions - just makes me kick myself for not thinking of it myself.

Perfect, thanks Andrew.