data type mismatch

[Maresdd]

I am trying to run the following code and keep getting a data type mismatch.
Can somebody please have a look and let me know where I'm going wrong.

Private Sub Command7_Click()

Dim ctl As Access.Control
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim Val As Currency
Set db = CurrentDb()
Set qdf = db.QueryDefs("qry_payrolledit")


For Each varItem In Me!LstPayroll.ItemsSelected
strCriteria = strCriteria & "," & Me!LstPayroll.ItemData(varItem) & ""
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = "DELETE tblpayroll.* " & vbCrLf & _
"FROM tblpayroll " & vbCrLf & _
"WHERE (((tblpayroll.Recnum) In (" & strCriteria & ")));"


DoCmd.OpenQuery "qry_payrolledit"
Set db = Nothing
Set qdf = Nothing

LstPayroll.RowSource = ""

DoCmd.SetWarnings True
Me.Employee.SetFocus

End Sub

 

[pietlinden]

From the looks of it, this is the problem:

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

Text values should be delimited with a single quote 'sample'
Const cQUOTE As String = " ' " ' extra spaces are for clarity...
don't actually put them in there.
For Each varItem In Me!LstPayroll.ItemsSelected
strCriteria = strCriteria & "," & cQUOTE & Me!LstPayroll.ItemData
(varItem) & cQUOTE & ""
Next varItem

 

[Maresdd]

thankyou so much. That was a very quick reply. Will check it out when I'm
back from holidays 8/2/10

 

[Maresdd]

I'm still getting the data type mismatch error. The line it fails on is

DoCmd.OpenQuery "qry_payrolledit"

 

[John Spencer]

is RecNum a Number field or is it a text field?

'If a number field then
For Each varItem In Me!LstPayroll.ItemsSelected
strCriteria = strCriteria & "," & Me!LstPayroll.ItemData(varItem)
Next varItem

'If a text field then
For Each varItem In Me!LstPayroll.ItemsSelected
strCriteria = strCriteria & ",""" & Me!LstPayroll.ItemData(varItem) & """"
Next varItem
strCriteria = Mid(strCriteria, 2)

strSQL = "DELETE tblpayroll.* " & _
"FROM tblpayroll " & _
"WHERE tblpayroll.Recnum In (" & strCriteria & ")"

'Add Debug.print to check validity of SQL string that is constructed.
'Once through testing, comment out or remove debug.Print line
Debug.Print strSQL

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County