The manipulation of SYS(3055) (which you suggested in your reply to Ed) doesn't apply to your problem. You got the somewhat misleading error message 'SQL command is too complex' because you put something in your IN() clause that was not a valid SQL statement. You had just a list of strings, similar to what the INLIST() function expects. A "SQL syntax error" message or something would have been more helpful, but these things happen. Anyhow, IN() is not a function like INLIST(), it's a clause of a SELECT - SQL which is supposed to contain another SELECT -SQL, that is to say, the subquery. If that subquery were very complicated (how complicated, I don't know), the limits could be affected by SYS(3055), I suppose. But you don't actually have that problem. If you have to plan for more than 24 criteria strings, you'll need code to put them in a cursor and to get to that cursor from your other form (which you mentioned in your reply to Ed). Might as well forget about INLIST().

The subquery is rushmore optimizable and can pull as big a cursor as you like, up to the usual vfp table limits. However, wherever you have

SELECT yourfields FROM yourtable WHERE searchfield IN (SELECT searched FROM searchcursor)

you can also use

SELECT yourfields FROM yourtable INNER JOIN searchcursor ON searchfield = searched

which may actually be faster (and Ed probably knows if it is).

By the way, if you're interested, you might search for Bruce Campbell's thread asking about alternatives to the NOT IN () clause, which is not Rushmore optimizable. Many were suggested and tested, and none were any better. Good thing you don't need that.

>Bret,
>
>>I have a feeling she was thinking of INLIST() when she used IN() that way. If the sequence of strings is short and constant, INLIST() would be easier. The help menu made me wonder if it is rushmore optimizable when INLIST() contains more than two parameters. My quick test confirms that it is.
>
> INLIST() function has 24 codes limitation (BTW, Ed Pikman was the first person, who let me pay attention here). I need 32 codes. In my wherexp I analyze the string lnOccurs=Occurs(lcString,',') and if it's more than 24, reconstruct user's criterion using IN function.
>
> My questions are:
> 1) Do you know about limitation in using IN and what is it?
> 2) Did you compare usage of subquery vs INLIST() function and what runs faster?
>
> TIA
>
>
>>If the sequence of strings was long or variable, using a subquery (with an actual SQL statement in it), as you suggest, would be better.
>>
>>>You may build a cursor on fly with records 'BROO','FOXB' etc and then
>>>select town from towns where town IN (select town from mycursor)
>>>
>>>
>>>>Walter,
>>>>
>>>> Recently I found another problem with SQL. In my case the Error message was 'SQL command is too complex'. I did a simple query like:
>>>>select town from towns where town IN ('BROO','FOXB',etc.), 32 codes.
>>>>Is my understanding correct, what I can not use IN function in SQLs? What the limitation of it's usage?
>>>>
>>>>TIA
>>>>
>>>>>