Filtering Based on Dates in Microsoft Access

Question

I am having issues filtering by date in Microsoft Access. The code below will show all entries with a valid date field (only filters entries with empty date field), not just the entries with a date after the selected date.

Private Sub Date_Filter_AfterUpdate()
Me.Filter = "[Table Name].[Date Field] > " & Me.Date_Filter & ""
Me.FilterOn = True
End Sub

Date_Filter is a text box on the form and is formatted as "medium date" along with the date field in the table.

I feel like this is a basic syntax error or something, but I cannot figure it out and search hasn't gotten me anywhere but further confusion between SQL, macros, vba, etc.

Update: Tried the following syntax change based on: Compare dates access sql

Me.Filter = "[Table Name].[Date Field] > #Me.Date_Filter#"

But got the following error:

Syntax error in date in query expression '[Table Name].[Date Field] > #Me.Date_Filter'.

Thanks!

Updated post with some syntax changes I tried, but did NOT work. — user3248621

Applecore Applecore · Accepted Answer · 2020-04-23T14:56:05

You need to concatenate the date into the filter:

Me.Filter="[Date Field]>=" & Format(Me.Date_Filter, "\#mm\/dd\/yyyy\#")

The use of the Format is to force the date to be unambiguous, as 03/04/2020 could be either 3 April or 4 March depending on the date settings.

Regards,

Filtering Based on Dates in Microsoft Access

1 Answers