How to Filter Data Between Two Dates in MS Access

Filter data between two dates is crucial in data analysis. It enables businesses to identify trends, track performance, and make informed decisions. This skill is vital for leveraging the full potential of data-driven decision-making.

We will use the [Hire Date] column in the Employees’ data as a reference to the date column.

Single Date Filter Criteria

VBA Code Method: A single-date filter criterion is a simple and easy approach with a textbox and a button.

Apply this code to the “On Click” event of the button.

Private Sub CmdSearch7_Click()
Dim strGenderFind As String
    'Criteria
    strGenderFind = "[Hire Date] = #" & Me.Search7 & "#"
    Me.Form.Filter = strGenderFind
    Me.Form.FilterOn = True
End Sub

Table Query Method: Build a query with all the necessary fields from the table and apply the Date Text box reference into the criteria of [Hire Date] field.

The button will only act to refresh the form. To clear the filter from the form, you have to empty the date textbox or a better approach is to create another button to clear the form which will be used to make the text box null and refresh the form.

Between Two Dates Filter Criteria

VBA Code Method: Create 2 text boxes and a button in the head section of your form and name them properly.

Apply this code to the “On Click” event of the button.

Private Sub CmdBetweenDt_Click()
    Dim strGenderFind As String
    'Criteria
    strGenderFind = "[Hire Date] between #" & Me.txtDtFrom & "# and #" & Me.txtDtTo & "#"
    Me.Form.Filter = strGenderFind
    Me.Form.FilterOn = True
End Sub

Table Query Method: Build a query with all the necessary fields from the table and apply the Date Text box references into the criteria of [Hire Date] field like this:

Between FormTxtDateFrom And FormTxtDateTo

In the above expression, both text boxes are the references of the date text boxes.

The button will only act to refresh the form. To clear the filter from the form, you have to empty the date textbox or a better approach is to create another button to clear the form which will be used to make the text box null and refresh the form.