CODEWITHSUNDEEP
ms-accessvba
Mark Pelletier
Mark Pelletier

Reputation: 1359

MS Access VBA - Filter form using Date

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!

Upvotes: 0

Views: 4714

Answers (1)

Gustav
Gustav

Reputation: 55831

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

Upvotes: 2

Related Questions