Best way to automate parameter passing to a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that prompts for entry of two different values when it is run,
a location and a cutoff value. I'd like to run this query multiple times
automatically using a range of cutoff values for a particular location. I
want to take these results and further analyze them - probably using Excel.

I figure that I can do this using Access VBA to generate a report, then
export the report, parse the file a bit and import back to Excel, but it
seems like there has to be a better way to do this. Any suggestions?

Thanks,

MJG
 
The easiest way I've found to automate parameter passing is by using the
Querydef object. In addition, another method very suitable for automation is
to leave the parameters out entirely and then use ReplaceWhereClause on your
query's recordset, building a specific SQL string at runtime. The book
"Access 2003 VBA Programmer's Reference" (Wrox pub) has an excellent section
on this.

Randall Arnold
 
If I understand you - do this --
Add output fiedls to your query for each.
Location: [Enter location] Cutoff Value:[Enter cutoff value]

Copy and paste them as criteria (omitting label and colon) so that the promt
is identical to output.
 
Karl,

Thanks for your reply. I have output fields that work as expected. I want to
automate the process of inputting data into them so I don't have to do it
manually, and (as importantly) get results in some consolidated format.

For example, for a location (in this case it is a distribution center and my
query parameters) I want to run the query 20 times using a range of cutoff
values (my other query parameter) from 1 to 20. If I do this manually I have
a lot of key pressing to do and my query results are doled out one at a time.
I'd like to automatically (likely programmatically) run the query 20 times
and get results for all 20 queries consolidated in one form, whether it be an
Access report, Excel spreadsheet, .csv file, .txt file, etc.

I hope this helps clarify what I am looking for.

Thanks,

Matt

KARL DEWEY said:
If I understand you - do this --
Add output fiedls to your query for each.
Location: [Enter location] Cutoff Value:[Enter cutoff value]

Copy and paste them as criteria (omitting label and colon) so that the promt
is identical to output.
MJG said:
I have a query that prompts for entry of two different values when it is run,
a location and a cutoff value. I'd like to run this query multiple times
automatically using a range of cutoff values for a particular location. I
want to take these results and further analyze them - probably using Excel.

I figure that I can do this using Access VBA to generate a report, then
export the report, parse the file a bit and import back to Excel, but it
seems like there has to be a better way to do this. Any suggestions?

Thanks,

MJG
Click to expand...
Click to expand...
 
Back
Top