Data Mismatch from Multi Select List box query

[NeilT123]

I am trying to use a Multi Select List Box to filter a form and have adapted the following code but the code does not work.

If I try to open the query QrySLTFieldDetails it says there is a data mismatch so I think the problem is that my value FarmAccountNumber is a number.

I have tried to lose the "" in the code but have failed so I wondered whether anyone could point me in the right direction?

Thank you in advance for any help

Private Sub Command4_Click()
Dim Q As QueryDef, DB As Database
   Dim Criteria As String
   Dim ctl As Control
   Dim Itm As Variant

    ' Build a list of the selections.
    Set ctl = Me![cboFmAccntNo]

    For Each Itm In ctl.ItemsSelected
       If Len(Criteria) = 0 Then
          Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
       Else
          Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
           & Chr(34)
       End If
    Next Itm

    If Len(Criteria) = 0 Then
       Itm = MsgBox("You must select one or more items in the" & _
         " list box!", 0, "No Selection Made")
       Exit Sub
    End If
    
   ' Modify the Query.
   Set DB = CurrentDb()
   Set Q = DB.QueryDefs("QrySLTFieldDetails")
   Q.SQL = "Select * From tblFieldDetails Where [FarmAccountNumber] In(" & Criteria & _
     ");"
   Q.Close


    ' Run the query.
DoCmd.OpenQuery "QrySLTFieldDetails"
End Sub

 

[MajP]

Each of those chr(34) is a double quotes. Your query is resolving to.

IN("1","7","9")
you need to get it to resolve to
IN(1,7,9)

You need a debug.print statement so that you can error check what the criteria looks like? That is how you error check a sql string prior to running it.

Where is your modified code?

 

[NeilT123]

Thanks MajP it was the chr(34)'s causing the issue