# Thread: String prepared for using IN (A2k3)

1. ## String prepared for using IN (A2k3)

Hi All,
I've got the following string that takes a column such as (entry1, entry2, entry3) and changes it to ("entry1", "entry2", "entry3") so it can be used with the IN operator; i.e. In("entry1", "entry2", "entry3").
I'm having mental blocks, however, and am trying to change it to an indefinite entry string. I mean the original string should have 1 to n entries and need to change it into ("entry1", "entry2", "entry3", ...., "entryn").

S = Trim(S)
lPos = InStr(S, ",")
If lPos = 0 Then Exit Function 'No Spaces in string!
strFirst = Left(S, lPos - 1)
lPos = InStr(Mid(S, lPos + 1), ",")
If lPos = 0 Then 'Only One Space!!
strSecond = Right(S, 3)
Else
strSecond = Mid(S, InStr(S, ",") + 2, InStr((Len(strFirst)), S, ",") + 1)
strThird = Right(S, 3)
End If

ReadyForIn = strFirst & Chr(34) & (", ") & Chr(34) & strSecond & Chr(34) + ", " & Chr(34) & strThird

Would someone please help me out in converting to the indefinite type?

Thanks.

gdr

2. ## Re: String prepared for using IN (A2k3)

If I understand the scenario correctly, you could use function similar to this:

Public Function GetInString(ByRef strList As String) As String

Dim tmp() As String
Dim lCount As Long
Dim n As Long
Dim strTmp As String

If Len(strList) > 0 Then
' Get rid of spaces:
strList = Replace(strList, Chr\$(32), vbNullString, , , vbBinaryCompare)

' Get array from comma-separated list:
tmp = Split(strList, ",")

' Get count of items in list:
lCount = UBound(tmp) + 1

For n = 0 To lCount - 1
strTmp = strTmp & Chr\$(34) & tmp(n) & Chr\$(34) & ","
Next n

GetInString = Left\$(strTmp, Len(strTmp) - 1)
Else
GetInString = vbNullString
End If

End Function

Sample results:

? GetInString("A,B,C,D,E")
"A","B","C","D","E"

? GetInString("ABC")
"ABC"

? GetInString("ABC, DEF, HIG, 123 ")
"ABC","DEF","HIG","123"

Function above uses Split function to get an array from items inlist, after removing spaces. I sometimes use something like this when generating IN string for SQL in code, only in that case you'd use a single-quote for the text delimiter. This assumes items in list to be parsed are text, not numbers or dates (for numbers do not use delimiter, for dates use # as delimiter). If some other results are desired, plz advise.

HTH

3. ## Re: String prepared for using IN (A2k3)

Hi Mark,
Works great! <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
Just what I needed for my tired brain. <img src=/S/yawn.gif border=0 alt=yawn width=15 height=15>

Thank you.

gdr

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•