Nadya,

There are special problems when testing for a value that is NOT IN because of the chance of their being a NULL value in the list of values to check for.

When there are nulls in the list, ANSI SQL-92 will always return the empty set. Rather than allowing the NOT IN syntax and risking this type of "surprise" result, FoxPro does not support the NOT IN syntax.

To test for values that are NOT IN the list one should use the syntax:

... WHERE NOT EXISTS ;
     (SELECT * FROM MyTable WHERE MyTable.Value = ValueImLookingFor)

Randall was asking how to test for values that are "IN" not values that are "NOT IN".

---

>>Randall,
>>
>>As Rox mentioned, INLIST works.
>>In SQL, IN also works:
>>

>>.... ;
>>    WHERE MyBank IN ("1234", "5678", "9012").
>>

>
>Hi Cindy,
>
> With this syntax could be some problems, unfortunately. Try it by yourself and you'll find them. Ed Pikman was the first, who told me about this. Especially for NOT IN ("1234", "5678", "9012")
>
>>You can also say:
>>

>>.... ;
>>    WHERE MyBank IN ;
>>    (SELECT Bank FROM Banks WHERE Banks.BankType = "SAVINGS")
>>

>>Thanks for posting the question.
>>

---

>>>I have a variable "cBank"
>>>
>>>I do this to assign things in a case statement
>>>
>>>cbank = "3850" and so on...
>>>
>>>I wanta case where the varibale can be either of the things i assign. i.e. - cBank = "0036" or cBank ="3855" or cBank ="3951" or cBank = "3850" or ;
>>> cBank = "3950" or cBank = "4369" or cBank = "1778"
>>>
>>>
>>>The variable is in a query as:
>>
>>SET DEFAULT TO (gc_datapath)
>> SELECT nsf.* ;
>> FROM nsf ;
>> WHERE BETWEEN(nsf.settdate, m.dStartDate, m.dEndDate) ;
>> and nsf.portfolio = alltrim(cBank) ;
>> ORDER BY bankname, mid ;
>> INTO table nsf_return_items.DBF
>>
>>>I get an Invlid count etc. error. when Ido this. How would I assign this variable in this case?
>>>
>>>Thanks in advance for your help.
>>>
>>>Randall