issue with .filter on a recordset
getting a run-time error 3464
"data type mismatch in criteria expression"
flagged on line Set rsFiltered = .OpenRecordset
first debug.print recordcount works fine and # is correct.
trying to automate the filtering of the rs.
have hard coded 204178 as my filter for job field.
this should return a record count of 7
can't get past the error.
Neil
Code:
Public Sub seqissue()
Dim rs As Recordset
Dim rsFiltered As Recordset
Dim strSQL As String
strSQL = "SELECT dbo_Job1.Job, dbo_Job_Operation1.Sequence, dbo_Job_Operation1.Work_Center, dbo_Job_Operation1.Status " _
& "FROM dbo_Job1 INNER JOIN dbo_Job_Operation1 ON dbo_Job1.Job = dbo_Job_Operation1.Job " _
& "GROUP BY dbo_Job1.Job, dbo_Job_Operation1.Sequence, dbo_Job_Operation1.Work_Center, dbo_Job_Operation1.Status, dbo_Job1.Status " _
& "HAVING (((dbo_Job1.Status)='active')); " _
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
rs.MoveFirst
With rs
Debug.Print .RecordCount
.Filter = "[Job]=204178"
Set rsFiltered = .OpenRecordset
rsFiltered.MoveLast
rsFiltered.MoveFirst
Debug.Print rsFiltered.RecordCount
.Close
End With
Set rs = Nothing
End Sub
