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

Francisco has an SQL Server example on his blog site. You might be able to
make something similar in Access:

http://sqlthis.blogspot.com/2005/02/list-to-table.html

D

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Arthur Fuller
Sent: Monday, November 21, 2005 8:23 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] SQL - IN Operator - Possible to use parameter


I wrote some code a while back that turns such a list into a temp table then
does a join to said temp table. I will have to dig it out, and when I do I
will post it, but Gustav has the basic framework correct.
I did it using a function that parses the list and returns a table. Then I
joined this table to the real tables.
I will go hunting now for this code and send it as soon as I find it.

Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: November 21, 2005 3:49 AM
To: accessd at databaseadvisors.com
Subject: Re: [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


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com