Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Grab comma delimited vals and wrap in quotes for sql IN function?

    I have a user form where they can filter the results by the Offer and by the Major. Since there can be several majors, i would like for them to be able to input 1 or more separated by commas: A,U,W. The issue is that when building the SQL string, each of those characters needs to be wrapped in ticks to work with the IN clause: ...IN('A','U','W'). Suggestions?

    Code:
        offer = Me.txtOffer
        major = Me.txtMajor
        strSQL = "Select * from tblMain WHERE [offer] = '" & offer & "' AND LEFT([Item Number],1) IN(" & major & ")"

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a possibility. You'll still have to test for txtOffer and txtMajor being empty, though.
    Code:
        Const c_SQL As String = "SELECT * FROM tblMain WHERE offer = '@O' AND LEFT([Item Number],1) IN ( @M );"
        
        Dim strSQL As String
        Dim strMajor As String
        Dim varMajor As Variant
        Dim i As Integer
        
        varMajor = Split(Me.txtMajor.Value, ",")
        For i = 0 To UBound(varMajor)
            If Len(strMajor) > 0 Then strMajor = strMajor & ", "
            strMajor = strMajor & "'" & varMajor(i) & "'"
        Next i
        strSQL = Replace(Replace(c_SQL, "@O", Me.TxtOffer.Value), "@M", strMajor)
    Have a nice day!

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    wow! works perfectly. I'm going to study this code, thanks.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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