none
Filter subform by week AND a category field selected. RRS feed

  • Question

  • Hey guys, I'm having issues getting the "this week" filter to work. For some reason the week starts from Sunday-Saturday and I just want a standard Monday-Friday. Here's the code I use currently:

    Dim dDate As Date
    
    Select Case optgDateChoice.Value
        Case 1: dDate = "1/1/1900"
        Case 2: dDate = "1/1/" & Year(Date)
        Case 3:
                Select Case Month(Date)
                    Case 1 To 3: dDate = "1/1/" & Year(Date)
                    Case 4 To 6: dDate = "4/1/" & Year(Date)
                    Case 7 To 9: dDate = "7/1/" & Year(Date)
                    Case Else: dDate = "10/1/" & Year(Date)
                End Select
        Case 4: dDate = Month(Date) & "/1/" & Year(Date)
        Case 5: dDate = Date - Weekday(Date) + 2
        Case 6: dDate = Date
    End Select
    
    'filter Dates with ALL Units selected
    With Me.cmbWardSearch
        If IsNull(.Value) Then
            Me.subVADAuditsReport.Form.Filter = "[DATE]>=#" & dDate & "#"
            Me.subVADAuditsReport.Form.FilterOn = True
        Else
    'filter Date selected AND Unit selected
            Me.subVADAuditsReport.Form.Filter = "[VADUnitDesc] = '" & Me.cmbWardSearch.Value & "' And [DATE] >= #" & dDate & "#"
            Me.subVADAuditsReport.Form.FilterOn = True
        End If
    End With

    The reason I have a date filter when "cmdWardSearch" is null is because the default of the optiongroup filter is "this week." Thoughts?


    • Edited by InnVis Sunday, August 18, 2019 11:27 PM
    Sunday, August 18, 2019 11:26 PM