"in" clause limitation ?

[JFGrenier]

I know that there is a limitation in the "where" clause of
about 40(....where ID = 123 and ID = 456 and ID = 789...)
Is this the same with the "in" clause ?

....where ID In (123,456,789................)

I'm opening a recordset with a dynamic sql string that may
require a few hundred clause on the same field ?

Is there a better way to do this ?

Merci!
Jean-Francois Grenier

 

[John Spencer (MVP)]

The in clause will "Or" the values not "AND" them. And yes there is a limit on
the number of characters (1024 characters comes to mind).

If you need many, many id's then you may have to populate a temporary table with
the Id's and do a join on that table. You could calculate how many you can
squeeze into an In clause by dividing 1024 by the size of your ID's. If the
Id's were all 4 characters plus a comma, then you could get roughly 200 id's
into the in clause.