>>Has anyone been able to get the IN clause to work with a view parameter? In the view designer filter if I have ctype IN C,D then it will correcly bring up all records with a ctype of either a C or a D. However, if I put ctype IN ?vp_ctype and run the query and put C,D in the view parameter I only get records with a C in the ctype (or if I put D first I get only records with a D in ctype).
>>
>>I need to be able use a view parameter to pull records that match a comma delimited list...anyone?
>>
>>Thanks!
>
>If this is a VFP table, then you can use
>
>lcTypeList = "C,D"
>and
>CREATE SQL VIEW myView AS;
>SELE * FROM MyTable;
>WHERE ALLTRIM(cType)$?lcTypeList

A couple of problems with this approach; one is that it matches "," as well (changing your code to have lcTypeList="CD" fixes that.) More importantly, iff the ALLTRIM() is really necessary, there's a strong implication that the cType field may not be a single character in length all the time, so the $ comparator might not be the right one to use here (an example of where $ would fail is with inexact matching, the string "CT" is not contained in lcTypeList, but ",D" is...)

If ALLTRIM(cType) is always exactly one character long, I'd recode it as follows:

lcTypeList = 'C,D'
lcSepChar = CHR(254)
lcTypeList = lcSepChar + STRTRAN(lcTypeList,",",lcSepChar + "," + lcSepChar) + lcSepChar
*
CREATE VIEW MyView AS ;
SELECT * FROM MyTable ;
 WHERE lcSepChar + ALLTRIM(cType) + lcSepChar $ ?lcTypeList

This cleanly delimits the $ comparator and works with variable-length values for cType (including an empty string.) The data entry can be variable length, too, so that the following list is valid for lcTypeList in the comparison:

lcTypeList = "C,D,,EF"

now matches "C", "D", all spaces, and "EF". The problem is, it's slow, never optimizable, cumbersome to code, and difficult to use outside of VFP applications, or to use at all in non-programmatic situations, where the user is entering the string by hand (again it's fixable by using a 'normal' separator character and explaining how to fill the parameter, but it's still ugly and error-prone in these situations.)