find value in list

[twcman]

I have a query in which I need find a value within a field of the table. Eg.
<cfquery name=&quot;testq&quot; datasource=&quot;#da#&quot;>
SELECT * FROM tblName WHERE #enteredvalue# IN(viewlist)
</cfquery>

viewlist is a column in the table with a comma delimited list as a field value. I am using msaccess. Does the IN() function work in access? If not, what would be the equivalent?

Chris

Chris Scott
The Whole Computer Medical Systems

 

[philhege]

Chris, comma-delimited lists in fields are a pain, a nuisance, and a general bummer, and really shouldn't be stored as such. They make you end up attempting backward comparisons like you're trying to do right now.

The &quot;normal&quot; way to use the IN() function is to ask if the field value of a table is contained in the list that's inside of IN():

WHERE MyField IN('this','that','theotherthing')

This also lends itself nicely to finding groups of records through a subquery:

SELECT myrecordstuff from Mytable where ID IN(select id from customerrequests where customerID = 143214231)

Now, for the reverse: it IS possible to do something like

WHERE myvalue IN(select mysearchfield from myothertable)

in which &quot;myvalue&quot; is a variable or constant and not a field name. BUT, &quot;select mysearchfield from myothertable&quot; won't evaluate comma-delimited lists in the field as distinct entities of the IN list. It just doesn't work that way.

You could use the LIKE operator, but you leave yourself open to anomalies. If your list is &quot;pie,crust,pieinthesky,crusty&quot;, and you were looking for &quot;rust&quot;, well, I think you see the problem.

The kluge is to query every record in the table for that field, CFLOOP through the field contents, construct another list, and then run the search query against that list. Or, just look for it while you're looping through the field contents. Messy any way you look at it.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....