Webmaster Forum / ASP / Database Access / May 2008

I am not sure, but i think it is also db dependand.

I know with oracle db it breaks at 1000

Jan Willem

Evertjan. wrote  on 17 May 2008 22:17:22 GMT:

How about dumping the values into a table and using a join them find out if
they exist in tblX?

Previous answers may have touched on what I'm about to post, which  is my
canned answer on this topic. Maybe it contains something the other responses
did not cover:

There are two solutions for this problem listed in the following KB article
(Q210530 - ACC2000: How to Create a Parameter In() Statement), found by
searching for the keywords "parameter list query" (no quotes) at
http://support.microsoft.com.

http://support.microsoft.com/support/kb/articles/Q210/5/30.ASP

The first solution uses Instr() to test the field values against the list in
the parameter. The second involves dynamically creating a SQL statement in
code.

Thanks to Paul Overway, here is a third solution, using the Eval function:

WHERE (((Eval([Table]![Field] & " In(" & [Forms]![Formname]![textboxname] &
")"))=True))

or, using a prompted parameter:

WHERE (((Eval([Table]![Field] & " In(" & [Enter List] & ")"))=True))

Thanks to Jeffrey A. Williams, here's a 4th solution:

If you don't mind adding a table to your database, and you're comfortable
dealing with possible multi-user issues, this will perform better than
either of the solutions that involve running a function (Instr or Eval) on
every row of your table:

Create a new table with two fields:

tblCriteria:
   Criteria    text
   Selected    boolean (yes/no)

Populate the table with your values and select a couple of items.  Now you
can use this table in your query as such:

Select * from table1
   inner join tblcriteria
       on table1.[your criteria field] = tblcriteria.criteria
where tblcriteria.selected = true

You can easily setup a form (or subform) that is bound to tblCriteria and
allow the users the
ability of selecting which values they want.

Thanks to Michael Walsh, here's yet another way:

SELECT Table3.ConName, Table3.State, Table3.Zip
FROM Table3
WHERE "," & [list] & "," LIKE "*," & [ConName] & ",*"

with [param]   some string like:   '1,4,5,7'

note that there is no space after the commas.

It works simply. If AccountID  is  45,  clearly    ',1,4,5,7,'  LIKE
'*,45,*'    returns false.
   If AccountID  is  4, on the other hand,   ',1,4,5,7,' LIKE  '*,4,*'
returns true.

So, you have, in effect, an IN( )  where the list is a parameter.