How do I pass a date parameter to an a macro which executes import of ds?

[margo1]

Can someone suggest how I should proceed with the following? I'm very
new to MS Access. I have three data sets that contain the same date in
each ds. I have a macro that imports data into a table using these
three data sets. I would like to automate my macro further, by passing
a date parameter that will be substituted as part of the data set, this
way I can import files for specific date. Presently the date portion
is hard coded. Thanks.

 

[John Vinson]

Can someone suggest how I should proceed with the following? I'm very
new to MS Access. I have three data sets that contain the same date in
each ds. I have a macro that imports data into a table using these
three data sets. I would like to automate my macro further, by passing
a date parameter that will be substituted as part of the data set, this
way I can import files for specific date. Presently the date portion
is hard coded. Thanks.

Don't change the macro - change the query.

Rather than a literal date on the Criteria line, put

[Enter date:]

When the query runs, it will prompt for the date.

John W. Vinson[MVP]

 

[margo430]

John,

I've created a parameter query, and in my macro I OpenQuery pointing to
my parameter query, but how do I pass that value to SetValue. The
Query is opened and the SetValue is not set. What am I doing wrong?
All I want is to get the value from the query and pass it on to my VBA
code.

 

[John Vinson]

John,

I've created a parameter query, and in my macro I OpenQuery pointing to
my parameter query, but how do I pass that value to SetValue. The
Query is opened and the SetValue is not set. What am I doing wrong?
All I want is to get the value from the query and pass it on to my VBA
code.

You don't need any Macro at all, not if you're using VBA code!

Try:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim prm As Parameter
Set db = CurrentDb
Set qd = db.Querydefs("YourStoredQuery")
qd.Parameters(0) = "Blah blah blah"

then for an Action query, use qd.Execute; to look at records, use

SET rs = qd.Recordset

John W. Vinson[MVP]