Solved: Import values (and query repetitive work )

[daemonskin1]

Hello Forum,

MS Access "wannabe" here... http://vbaexpress.com/forum/images/smilies/banghead.gif

I wonder if someone can help me with this particular problem:

I want to run query always against the same table without always performing repetitive manual tasks:

Here is the schema:
-------------------
SELECT ... FROM ...

WHERE something = "x"

OR something = "y"

OR something = "z"

or ...

-----------------

Next time I want to run same query but:

SELECT ... FROM ...

WHERE something = "x1"

OR something = "y2"

OR something = "z2"

or ...

-----------------------------------

Next, would be the same query against x3, y3, z3 and so forth.

Now, how can I import values I want to query against(lets say new values x4, y4, z4...) at the particular place in the query (in between "") without each time manually doing the typing? That is, I would like to import x4, y4, z4 from the notepad or Excel Spreadsheet into that same query. Obviously, if I have hundreds of values to query against this can be laborious work doing it each time manually.

Thanks for the help!

[geekgirlau]

You can have a parameter query, where you dynamically enter the criteria values. In principle it works as follows:

Build your query and test it using your first set of criteria
Once you are happy with the way it works, replace the actual criteria with a parameter. For example, if you were searching on a suburb, replace the name of the suburb in your test with "[Suburb]" (use a name that is not the same as the field name)
In the query design window, select Query | Parameters. Enter the parameter (eg "Suburb") and the type of data you are expecting.
If you run the query now, it will prompt you with "Suburb" and wait for you to enter the criteria.

Now to run it numerous times, changing the criteria each time, you need to do something like the following:

Sub LoopQuery()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rec As DAO.Recordset

Set db = Currentdb()
Set qdf = db.QueryDefs("MyQuery")
qdf.Parameters("Suburb") = "criteria 1"

' option for action query
qdf.Execute

' alternative option to return recordset
Set rec = qdf.OpenRecordset(dbOpenSnapshot)


ExitHere:
On Error Resume Next
rec.Close
qdf.Close
db.Close

Set rec = Nothing
Set qdf = Nothing
Set db = Nothing
End Sub

You can easily set up a loop to populate the parameter with different values each time.

Please note that you can only use "execute" if it's an action query - if you want to just return records, you create a recordset instead, as in the second option in the above example.

[daemonskin1]

That will do it! Thanks a lot geekgirlau!