Iterating ItemsSelected and Generating A Report from it___Access VBA

April 12, 2022

Good day Elders,

I am having another issue. I have a listbox that is populated by the value of a textbox. When I click on multiple items and run my query, I get a blank result. The field data types are all ShortText.

If I pick just one item, the query works just fine. Here is my sample code below, kindly assist please.

 Private Sub txt_testexport_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim StrSQz As String
Dim strx As String
Dim lst As ListBox
        
Set db = CurrentDb()
Set qdf = db.QueryDefs("qry_export")
Set lst = [Forms]![frm_search]![lst_rec]

For Each varItem In Me!lst_rec.ItemsSelected
strx = strx & "," & Me!lst_rec.ItemData(varItem) & ""
Next varItem

If Len(strx) = 0 Then
        MsgBox "Nothing Selected from List" _
            , vbExclamation, "Nothing to find"
Exit Sub
End If

strx = Right(strx, Len(strx) - 1)
StrSQz = "SELECT TargetCDRs.OtherParty, TargetCDRs.TargetNumber, TargetCDRs.Description, 
 TargetCDRs.Duration, TargetCDRs.StartDateTimeLocal, " & _
         "TargetCDRs.EndDateTimeLocal, TargetCDRs.Direction, TargetCDRs.SubType " & _
         "FROM TargetCDRs " & _
         "WHERE ((TargetCDRs.OtherParty)=[Forms]![frm_search]![txt_rec]) AND 
(TargetCDRs.TargetNumber IN('" & strx & "'));"

qdf.SQL = StrSQz
       
DoCmd.OpenQuery "qry_export"


Set lst = Nothing
Set db = Nothing
Set qdf = Nothing

End Sub

[Form Sample][1]
[1]: https://i.stack.imgur.com/sq3V7.png

>Solution :

Single quotes are needed:

strx = strx & ",'" & Me!lst_rec.ItemData(varItem) & "'"

' <snip>

"WHERE (TargetCDRs.OtherParty=[Forms]![frm_search]![txt_rec]) AND 
(TargetCDRs.TargetNumber IN(" & strx & "));"