[AccessD] SQL - IN Operator - Possible to use parameter

Hi Borge

No you can't do that. Passing more "parameters" will equal to something like:

  WHERE ShipRegion In ('Avon,Glos,Som')

which, as you have seen, results in zero records.

You have several options:

1. Retrieve the SQL property of the query, adjust it to include the values of your parameters, and apply the modified property.

2. Write your parameter values to a temp table and include this in your query.

3. Rewrite the IN clause to a series of IIF() clauses each including one of your parameter values. This will only do if you have a fixed and/or limited number of parameters.

4. Build a custom function to which you pass ShipRegion and your parameters. If a value of ShipRegion matches a parameter value, the function returns True, otherwise False. Include the function in your Where clause.

5. As 1 but - if it is not used for any other purpose - write the complete query in code. This is what I mostly do.

/gustav


>>> pcs at azizaz.com 21-11-2005 09:17 >>>
Access - SQL:

Is it possible to turn a select query using the IN operator into a parameter
query?

Like in :
SELECT *
FROM Orders
WHERE ShipRegion In ('Avon','Glos','Som')

something like :
SELECT *
FROM Orders
WHERE ShipRegion In ([Criteria:])

which only returns rows when I enter one criterium - any attempts of two or more
criteria returns no rows.....

I am using two select queries both using the IN operator as basis for a third
result query (left out join between the two query statements).

The only way to parametize the two select queries appears to be via vba code -
creating the querydefs each time... or?

/borge