Find if field contains number

Hope this makes sense...

I have a table with a field containing a collection of numbers, each separated by a controlled character (which could be anything, like a space, comma, dash, underscore, colon, etc.), so for example one record could be:

1,2,9,13,7,4

This string will be assigned to a variable, I then want to compare this to another, single number, such that if the single number is contained within the first string, the record is displayed, otherwise it is ignored, eg:

strLotsOfNumbers = 1,2,9,13,7,4

If strLotsOfNumbers (contains) rs("SingleNumber") Then (display rs("FieldName"))

However, it also has to compare the entire number between separators, so in the example above if rs("SingleNumber") = 3 it would return FALSE, ie, wouldn't match the '3' with the second character of the '13'.

If necessary I can start, end, or both, the long string with the control character, like: ,1,2,9,13,7,4, so each number is surrounded.

Thanks in anticipation

Edited by pedalcars

Another way of doing it would be to split up the number list into an array and then use a for loop to compare the numbers. This method requires a bit more code then the method bonnett mentioned, but at least this way you wouldn't be dependant upon comparing strings, which can get messy if you get extra characters in there, such as if you end up with a space between commas, then the method above would not work unless you specifically checked for that.

temparray = split(strLotsOfNumbers, ",")
For loopcounter = 0 to Ubound(temparray)
    If Int(temparray(loopcounter)) = intNumberToCompare Then
        ...
        Exit For
    End If
Next