Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try changing

    "WHERE InStr('n',[visit_stat])<>0 "

    to

    "WHERE InStr('n',[visit_stat])<>0 AND [skill] Not In ('RN','PT') AND [pay_unit]='V'"

    BTW, are you sure it shouldn't be InStr([visit_stat],'n') instead of InStr('n',[visit_stat])?

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    >>BTW, are you sure it shouldn't be InStr([visit_stat],'n') instead of InStr('n',[visit_stat])?
    [/quote]

    LOL! Hans, I'm the last person to ask this of. The report was set up for by by Francoise (sp) and worked wonderfully and I certainly never questioned it. At that time, the code didn't even make sense to me. At least now I can understand it a little bit better.

    So, here is where I'm at. I keep getting an error that there are no records. I wasn't sure which new filter would be causing it so I took out the filter on [pay_unit]='V' and it ran. When I kept in [pay_unit and took out the PT and RN filter, it ran. There is something about having both filters in that results in no records error.

    Also, to add to the confusion, I need to report to filter PT and RN [skill] that have [pay_unit] as "V". If [pay_unit] is "H" then it should ALWAYS stay in the report. I didn't catch that yesterday as I started working on this.

    I'm uploading a stripped down version of the DB.

    Thanks,
    Leesha
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try changing the line with WHERE ... to

    "WHERE InStr('n',[visit_stat])<>0 AND (([skill] In ('RN','PT') AND [pay_unit]='V') OR [pay_unit]='H')"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •