Form Filter Current Week (1 Viewer)

dgaller · Feb 22, 2018

I am working on filter for a subform based on a combo box. The only issue I am having is filtering for current week (in red below). I think it may be a syntax issue but I am not sure.

Can anyone help?

Code:

Public Sub TskDateFilter()

DoCmd.RunCommand acCmdRemoveFilterSort
Me.Combo25 = ""
Me.Combo19 = ""
Me.Combo33 = ""



If Me.[Combo38] = 1 Then
Me.[TSKlookup subform].Form.Filter = "([DueDate] <= Date()) and ([Status]=1)"
Me.[TSKlookup subform].Form.FilterOn = True

ElseIf Me.[Combo38] = 2 Then
Me.[TSKlookup subform].Form.Filter = "([DueDate] < Date()) and ([Status]=1)"
Me.[TSKlookup subform].Form.FilterOn = True

ElseIf Me.[Combo38] = 3 Then
Me.[TSKlookup subform].Form.Filter = "([DueDate] = Date()+1) and ([Status]=1)"
Me.[TSKlookup subform].Form.FilterOn = True

ElseIf Me.[Combo38] = 4 Then
[COLOR="Red"]
Me.[TSKlookup subform].Form.Filter = "[DueDate] = DatePart(""ww"", [DueDate]) = DatePart(""ww"", Date())"
Me.[TSKlookup subform].Form.FilterOn = True[/COLOR]

ElseIf Me.[Combo38] = 5 Then
Me.[TSKlookup subform].Form.Filter = "[Status] = 1"
Me.[TSKlookup subform].Form.FilterOn = True

ElseIf Me.[Combo38] = 6 Then
Me.[TSKlookup subform].Form.Filter = "([DueDate] = Date()+30) and ([Status]=1)"
Me.[TSKlookup subform].Form.FilterOn = True


End If

Minty · Feb 22, 2018

Use single quotes around 'ww'
The double quotes will break things.

dgaller · Feb 22, 2018

Thank you! Now I don't get an error, however the filter doesn't show any records? When I know I have some from this week.

Minty · Feb 22, 2018

Actually that doesn't surprise me having read it again, I don't think the logic is correct.
I think the filter should be something like

Code:

DatePart('ww' [DateDue]) = DatePart('ww', Date())

And from a readability point I would probably use a Select Case like;

Code:

Public Sub TskDateFilter()

    DoCmd.RunCommand acCmdRemoveFilterSort
    Me.Combo25 = ""
    Me.Combo19 = ""
    Me.Combo33 = ""


    Select Case Me.Combo38
    Case 1
        Me.[TSKlookup subform].Form.Filter = "([DueDate] <= Date()) and ([Status]=1)"
        
    Case 2
        Me.[TSKlookup subform].Form.Filter = "([DueDate] < Date()) and ([Status]=1)"
        
    Case 3
        Me.[TSKlookup subform].Form.Filter = "([DueDate] = Date()+1) and ([Status]=1)"

    Case 4
        Me.[TSKlookup subform].Form.Filter = "(DatePart('ww', [DateDue]) = DatePart('ww', Date()) )"
        
    Case 5
        Me.[TSKlookup subform].Form.Filter = "[Status] = 1"
        
    Case Else
        Me.[TSKlookup subform].Form.Filter = "([DueDate] = Date()+30) and ([Status]=1)"
    
    End Select
    
    Me.[TSKlookup subform].Form.FilterOn = True

End Sub

dgaller · Feb 22, 2018

Awesome, thank you! I learned something new and this is much cleaner!

For future users I had to modify case 4 as shown below.

Code:

Case 4
        Me.[TSKlookup subform].Form.Filter = "(DatePart('ww'[COLOR="Red"][B], [DueDate][/B][/COLOR]) = DatePart('ww', Date()) )"

Works perfect, Minty I truly appreciate the help!

Form Filter Current Week (1 Viewer)

dgaller

Registered User.

Minty

AWF VIP

dgaller

Registered User.

Minty

AWF VIP

dgaller

Registered User.

Similar threads

Users who are viewing this thread