Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

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