IN operator usage

[contiw]

I would like to determine whether the value of an expression is equal to any of the values of a list contained in a specified column field:

Suppose "fieldname" contains (value1, value2, ....)

What is the correct syntax for:

SELECT *
FROM Orders
WHERE ShipRegion In (fieldname)

Thank You very much.

 

[PHV]

WHERE InStr(',' & fieldname & ',', ',' & ShipRegion & ',') > 0


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

If you really have a field with values like:

Fieldname
Value1, Value2, Value....

you need to re-think the design if possible. Storing multiple values in a single field breaks normalization rules and you will find that it gets harder and harder to extract the information you need.

You may want to read The Fundamentals of Relational Database Design.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual