Searching through a comma delimited list field

[yippie_ky_yay]

Hello,

I get a field (called "range") from another database (which I have no control over) that gives me a list of numbers delimited by a comma (ie. 1,4,11). I need to query on these now and I'm not quite sure how to proceed. I didn't think it would be so bad because it's a list, but I just can't figure out how I can use it.

Say someone selected "1" as the range criteria - this is about as close to the answer as I can get:
Select * From myQuery WHERE range like "*1*";

Obviously this would pick up the 11 as well which would be wrong.

Can someone help me out please?

TIA

-Sean

 

[dcx693]

If you have Access 2000+, you can use the Split function to write the string contents to an array. You can then use some code to search the array for your item. There is no built-in way of sorting arrays in Access, so if your list is long, you might want to consider saving it to a table and running a query on it.

Or, I guess you could try to be clever with the string searches. If you're looking for 1 in your string, you just have to make sure it's the left most element with a comma next to it, the rightmost element with a comma to the left, or it's enclosed within 2 commas. It could wind up being a pain.

 

[EMP]

Select * From myQuery WHERE "," & Range & "," Like "*,1,*";

 

[dcx693]

Select * From myQuery WHERE "," & Range & "," Like "*,1,*";

Except if 1 is the first (or last) item in the list.

 

[yippie_ky_yay]

Thanks guys - this just might work (leaving to go home now - but I'll try this in the morning).

The list is always in order, so I'll only need to look for the first.

Cheers!

-Sean

 

[EMP]

Except if 1 is the first (or last) item in the list.

No need to worry.

"," & Range & "," will take care of them, even if 1 is the only number in the field.

_

 

[yippie_ky_yay]

Well, I don't really get how it's working - but it's working!

Thanks to you both!

-Sean