MSAccess VBA, filtering query results via combo box (text)

Question

I want to filter date values (text) in a query based on a comboBox text value. Here is what I have:

ComboBoxTimePeriod (ID [num], TimePeriod [text]).

In ComboBox I select Time period, like "16.10.2017-15.11.2017".

In a query I have Date field with single date, like "20.10.2017" (text).

What I want is to write an SQL code which searches for all records with date within TimePeriod range.

So far Idea is to extract SatrtDate and EndDate from TimePeriod like this:

    Dim strStartDate As String
    Dim strEndDate As String

    strStartDate = Mid(Me.cboTimePeriod.Text, 1, 10)
    strEndDate = Mid(Me.cboTimePeriod.Text, 12, 10)

Now from TimePeriod "16.10.2017-15.11.2017" I have StartDate (16.10.2017) and EndDate (15.11.2017).

And I want to filter all records which have date within those two dates. This is where I need you guys.

Note that this is different issue from my last question, where I searched for a records with TimePeriodID (Subform filtering based off multiple parameters (Combobox AND Textbox)).

Gustav Gustav · Accepted Answer · 2018-03-12T19:07:18

Always handle dates as Date, not text:

Dim StartDate As Date
Dim EndDate As Date
StartDate = DateValue(Split(Me.cboTimePeriod.Value, "-")(0))
EndDate = DateValue(Split(Me.cboTimePeriod.Value, "-")(1))

The build your filter:

... " Between #" & Format(StartDate, "yyyy\/mm\/dd") & "# And #" & Format(EbdDate, "yyyy\/mm\/dd") & "#"

To filter a form:

Me.Filter = "[Date Field] Between #" & Format(StartDate, "yyyy\/mm\/dd") & "# And #" & Format(EbdDate, "yyyy\/mm\/dd") & "#"
Me.FilterOn = True

And do make sure that your Date Field is of data type Date, not Text.

MSAccess VBA, filtering query results via combo box (text)

1 Answers