Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012

    Unanswered: strcriteria treating list items as fields

    Hi, I am using basically the same vba code in my database. It runs a query based on criteria from a multi-select list box. When I click to invoke the query, the strcriteria treats the items selected as field names rtaher than items from the list. This is the excerpt from my code.

    strCriteria = right(strCriteria, Len(strCriteria) - 1)
    strSQL = "SELECT * FROM Oasis_Data_Clean_Fin " & _
    "WHERE Oasis_Data_Clean_Fin.Service in (" & strCriteria & ")"

    After I run the query, which ends up with no results, I go into teh query design mode and the criteria line shows this :

    In ([Arrive],[Ground])

    If I change this to In ("Arrive","Ground") the query runs as expected.

    How do I get he vba code to not enter the [brackets] around the criteria?


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    dunno, I'm guessing the square bracjets are coming for elsewhere in your code
    however the problem is that you are not quoting your string literals

    but to give you an answer to that we'd need to see how or where you builds strCriteria
    you can use either " or ' to quote the string literals

    whether you do it as
    strCriteria = strCriteria & ", '" & avariable & "'"
    strCriteria = strCriteria & ', "' & avariable & '"'
    strCriteria = strCriteria & ", " & chr$(34) & avariable & chr$(34)
    strCriteria = strCriteria & ", " & chr$(39) & avariable & chr$(39)
    is upto you
    Last edited by healdem; 05-04-12 at 14:41.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2012


    Here's the code I'm using. The multi-select list box values I select are named Arrived and Flip. Query3 in design mode shows these as In ([Arrive],[Flip]) but the criteria below shows them as in (" & strCriteria & ")"

    Private Sub Command328_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Query3")
    For Each varItem In Me!Task.ItemsSelected
    strCriteria = strCriteria & "," & Me!Task.ItemData(varItem) & ""
    Next varItem

    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything." _
    , vbExclamation, "Nothing to find!"

    Exit Sub
    End If

    strCriteria = right(strCriteria, Len(strCriteria) - 1)
    strSQL = "SELECT * FROM [Oasis_Data_Clean_Fin] WHERE [Oasis_Data_Clean_Fin].Service in (" & strCriteria & ")"
    qdf.SQL = strSQL

    DoCmd.OpenQuery "Query3"
    Set db = Nothing
    Set qdf = Nothing
    End Sub

Tags for this Thread

Posting Permissions

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