Limitation using IN in WHERE

X

XP

I am using Office 2003 on Windows XP.

In my WHERE clause of a SQL SELECT, I have an IN, for example:

A.FUND IN ('101', '102', '103')

In reality, I have over 60 funds in the list. I have found that I cannot
list this many items using IN or I get an error.

If I just use a few funds in the IN clause it runs fine, so I know it has to
do with this clause. Does anyone have a good work-around or suggestion to
resolve this issue?

Thanks much in advance.
 
X

XP

Sorry, I forgot to mention, I cannot use BETWEEN as there are skipped funds
in the list. Thanks.
 
M

Michel Walsh

Hi,


Enter the list in a small table you would create just for that (one column,
60 records). Then, use


Fund IN ( SELECT fieldname FROM tableName)


where fieldname and tablename are the field of the said table.



Hoping it may help,
Vanderghast, Access MVP
 
G

GPO

You might also want to see if you get better performance out of using joins
between the two tables instead of the IN(SELECT...). Certainly an outer
join/IS NULL will kill a NOT IN (SELECT...).
 
Top