Hi,
I am trying to link use a multi select listbox to select records to be displayed in a query and later a report. Currently, the criteria gets to the query, but it comes out as "1" or "2" or "4" (whatever ID numbers I select). It works if I go in after the fact and delete the quotation marks. I am not sure how to remove the quotation marks using my code though. Any help would be greatly appreciated.
My listbox is called list68. The table that the listbox and query get their data from is Doctors. The query is called Copy. The button causing the action is command6.
Code:
Private Sub Command6_Click()
On Error GoTo Err_Handler
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("Copy")
For Each varItem In Me!List68.ItemsSelected
strCriteria = strCriteria & "Doctors.ID = " & Chr(34) _
& Me!List68.ItemData(varItem) & Chr(34) & "Or "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
strSQL = "SELECT *, [Doctors].[First Name], [Doctors].[Last Name], [Doctors].[Company], [Doctors].[Address], [Doctors].[City], [Doctors].[State], [Doctors].[Zip/Postal Code], [Doctors].[Business Phone] FROM Doctors " & _
"WHERE " & strCriteria & ";"
qdf.SQL = strSQL
DoCmd.OpenQuery "Copy"
Set db = Nothing
Set qdf = Nothing
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Handler
End Sub
Thanks again