Hi,
I was given this code many years ago by this forum and its worked like a charm. I now need to do some futher filtering but keep getting errors. The code lies behind a report. When the report loads it is in a fomat that lays out the data with weeks across the top and hours in the day down the right column. It’s used for scheduling. Presently all levels of staff are in the report. I need the report to filter out “RN” and “PT” frin [skill] where [pay_unit] = “V”.
This is the code. I’ve bolded the code and made it red where the present filter lies and the additional filters need to be added. I kept getting the syntax wrong and was’t sure if I needed comma’s, semi-colons etc.
Thanks!
Leesha
Private Sub Report_Open(Cancel As Integer)
Dim DB As DAO.Database
Dim rstVP As DAO.Recordset
Dim rstWVR As DAO.Recordset
Dim strSQL As String
strSQL = “Delete * From tmpWeeklyVisit”
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Set DB = CurrentDb
strSQL = “Select cl_last, cl_first, Skill, StartTime,EndTime, dayofwk ” & _
” From tblVisitReport ” & _
“WHERE InStr(‘n’,[visit_stat])0 “
Set rstVP = DB.OpenRecordset(strSQL)
Set rstWVR = DB.OpenRecordset(“tmpWeeklyVisit”, dbOpenDynaset)
rstVP.MoveLast
rstVP.MoveFirst
Do While Not rstVP.EOF
rstWVR.FindFirst “[starttime] = #” & rstVP!StartTime & “#”
If rstWVR.NoMatch Then
rstWVR.AddNew
rstWVR!StartTime = rstVP!StartTime
Else
rstWVR.Edit
End If
rstWVR(rstVP!dayofwk) = rstWVR(rstVP!dayofwk) & rstVP!cl_last & “, ” & rstVP!cl_first & ” – ” & _
rstVP!skill & vbCrLf & Format(rstVP!StartTime, “hh:nn AM/PM”) & ” To ” & _
Format(rstVP!EndTime, “hh:nn AM/PM”) & vbCrLf & vbCrLf
rstWVR.Update
rstVP.MoveNext
Loop
Set rstWVR = Nothing
Set rstVP = Nothing
Set DB = Nothing
End Sub
