MS Access VBA - Filter form using Date

Question

I am wrestling with a task to filter a form based on a date criteria. I think I am munging the date format somehow and cannot find a combination that will return a result. There are valid dates on the subform; criteria should return a result - but returns ZERO records. I tried the DATE function as well as explicit #10/17/2017# type values.

Dim strFilter As String

Select Case Me!frmFilter.Value

    Case 1  'All
        Forms![InventoryList].[InventoryList subform].Form.FilterOn = False
    Case 2  'Active
        strFilter = "Forms![InventoryList].[InventoryList subform].Form.[StartDate] > #" & Date & "#"
        Forms![InventoryList].[InventoryList subform].Form.Filter = strFilter
        Forms![InventoryList].[InventoryList subform].Form.FilterOn = True
    Case 3  'Pending
        'do something else

End Select

End Sub

Any suggestions to help me move this task forward a little bit?

Thanks!

"StartDate" is coming from a Query, formatted as "General Date".. The source table for the query is defined as "Date/Time". — Mark Pelletier
– Mark Pelletier, Commented Oct 17, 2017 at 19:05

Gustav · Accepted Answer · 2017-10-17 20:55:11Z

2

This has to work if StartDate is a date and [InventoryList subform] is the name of the subform control:

Select Case Me!frmFilter.Value
    Case 1  'All
        Forms![InventoryList]![InventoryList subform].Form.FilterOn = False
    Case 2  'Active
        strFilter = "[StartDate] > Date()"
        Forms![InventoryList]![InventoryList subform].Form.Filter = strFilter
        Forms![InventoryList]![InventoryList subform].Form.FilterOn = True
    Case 3  'Pending
        'do something else
End Select

answered Oct 17, 2017 at 20:55

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Pelletier Over a year ago

Thank you. Works just as expected. Eric was also on the right track, but still I missed the bus and over-complicated the filter.

Collectives™ on Stack Overflow

MS Access VBA - Filter form using Date

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related