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
CodePudding user response:
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 & "));"