MS Query with Range of Parameters

[sterlecki]

What is the best way to use MS Query to query against an Access Table to return the data from a range of cells as a parameter for a field in the query.

Example: I would like each of the values for say sheet(1).Range(a1:a10) to be used as a parameter on the Access table, return the row of data and then move to the next cell(a2) as the next parameter and return the results to the next row down on the sheet with the query results.

When I select a range of cells at the prompt it says I can only select a single cell as a parameter. I'm looking for something like and In statement or a For Each type of set up.

I am just learning about MS Query so I'm looking for some helpful suggestions.

 

[SkipVought]

Hi,

You cannot use the PARAMETER feature of MS Query is you have a LIST, as the SQL statement is different in these instances, ie

Where FieldName = ?

vs
Where FieldName IN (YourList)

It can only be done with VBA (macro) code.

If your list is STATIC, U can give you a simple function that produces a list that you can PASTE into your query SQL code ONE TIME.

However, it appears that you want to provide a dynamic list to your query. If that's the case, you'll need a procedure to run whenever your list changes. In that event, here's what you need to do.

TURN ON your macro recorder.

EDIT your query thru to the MS Query grid

File > Return data to Excel from MS Query.

Turn off your macro recorder.

COPY your recorded code and post your question and recorded code in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sterlecki]

MS Query with Range of Parameters (thread68-1634657)

Has now been reposted as [highlight]thread707-1634682[/highlight]