How do you do a query to find a 1-digit number in a comma separated list?

[nvrivers]

I am trying to figure out how to do a query in Access 2000 that will find all records that contain the number 1. The problem is that the particular field I am searching has lists of numbers in it separated by commas. For example, the field may contain the numbers 1,71,38,81. (The field is formatted as a text field) I want to pull up all the records that contain the number 1, but if I search for 1, I also get records that contain 71 or 61 or some other number that contains a 1 as one of its digits.

I WOULD want it to return the above example, because it contains a 1 by itself, but I would NOT want it to return the following: 61,83,85 because it does not contain the number 1 by itself.

Is there some way to specify that I am searching for a single character? That would mean YES to "1" but NO to "61"?

[JKing]

Have you tried using the like operator and specifying the commas.

Example: WHERE field LIKE ',1,'

[mlcampeau]

Have you tried using the like operator and specifying the commas.

Example: WHERE field LIKE ',1,'

I had the same thought as JKing, but you will want to take into consideration that 1 could be in the first place in the list or last place as well. JKing's example would take into consideration that 1 is in the middle of the list. To take care of the other situations try this:

WHERE field LIKE '1,*' OR field LIKE '*,1,*' OR field LIKE '*,1'

[missinglinq]

I think the more important question here is exactly what kind of data are you storing in this field? It sounds like you're storing multiple values in a single field which is very, very poor practise, and leads to all kinds of problems, as I believe you've already discovered. Perhaps we could offer you an alternative approach if we knew a little bit more about your situation.

Linq ;0)>