none
Filtering Query with a Multi Value Field RRS feed

  • Question

  • I am hoping someone can help me with this. I was successful with the code for one field but for some reason I can't get this to work :(. Can someone take a look and see what the issue might be? I am getting a runtime error of "Syntax error in string iin query expression tbl_Impact.[Impact] IN('Cost; 'Schedule);

    Code below and thank you for any help!


    Private Sub Command0_Click()
    Dim varItem As Variant
    Dim strImpact As String
    Dim strSQL As String
    Dim cat As New ADOX.Catalog
    Dim cmd As ADODB.Command


    For Each varItem In Me.ImpactZ.ItemsSelected
            strImpact = strImpact & ",'" & Me.ImpactZ.ItemData(varItem) _
            
            
    Next varItem


    If Len(strImpact) = 0 Then
        strImpact = "Like '*'"
        
    Else
        strImpact = Right(strImpact, Len(strImpact) - 1)
        strImpact = "IN(" & strImpact & ")"
        
    End If

    strSQL = "SELECT tbl_Impact.* FROM tbl_Impact " & _
              "WHERE tbl_Impact.[Impact] " & strImpact & ";"
              
    MsgBox strSQL

    cat.ActiveConnection = CurrentProject.Connection
    Set cmd = cat.Views("Query01").Command
    cmd.CommandText = strSQL
    Set cat.Views("qry_Impact").Command = cmd
    Set cat = Nothing



    End Sub

    Thursday, March 31, 2016 6:09 PM

Answers

  • A couple things...

    One, you'll need a space between IN and ( in strImpact = "IN(" & strImpact & ")"

    Two, you'll need to enclose each of the string values in your IN clause in quotes, e.g.

     strImpact = strImpact & ",'" & Me.ImpactZ.ItemData(varItem) & "'"

    (your code leaves off the trailing quote on each)

    Finally you don't need the closing ";" at the end of your SQL statement although it would work fine either way.

    Hopefully this gets you on the right track!

    -Bruce

    • Marked as answer by JLB43 Friday, April 1, 2016 12:52 PM
    Thursday, March 31, 2016 7:44 PM

All replies

  • A couple things...

    One, you'll need a space between IN and ( in strImpact = "IN(" & strImpact & ")"

    Two, you'll need to enclose each of the string values in your IN clause in quotes, e.g.

     strImpact = strImpact & ",'" & Me.ImpactZ.ItemData(varItem) & "'"

    (your code leaves off the trailing quote on each)

    Finally you don't need the closing ";" at the end of your SQL statement although it would work fine either way.

    Hopefully this gets you on the right track!

    -Bruce

    • Marked as answer by JLB43 Friday, April 1, 2016 12:52 PM
    Thursday, March 31, 2016 7:44 PM
  • >>> I was successful with the code for one field but for some reason I can't get this to work :(. Can someone take a look and see what the issue might be? I am getting a runtime error of "Syntax 

    error in string iin query expression tbl_Impact.[Impact] IN('Cost; 'Schedule);<<<

    According to your description, I suggest that you could use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to copy the value of the SQL in the string before using it, then run a query directly from SQL view and paste your copied SQL string in place of what's already there and run it to see what happens, or you could create query design view then switch SQL view to get SQL string.

    In addition Contain one of the values in a list of values:

    In("France", "China", "Germany", "Japan")

    For more information, click here to refer about Examples of query criteria


    Friday, April 1, 2016 6:03 AM
  • It worked! Thank you so much for your quick response, it was much appreciated. 
    Friday, April 1, 2016 12:53 PM