ACCESS 2007 - Form / subform filtering between dates

Question

In ACCESS 2007 I have a main form with two unbound comboboxes named cmbStavOd and cmbStavK, both formatted as Short Date. I have a subform named frm_qryNaklady_subform, based on a query with a field named datNakladDatum (textbox name datNakladDatum), formatted as Short Date. I'd like to allow users to enter a date range and have the subform filtered to only show records of the date range entered in the main form boxes. I'm using a VBA code in AfterUpdate event like:

Dim datRokMesiacOd As Date
Dim datRokMesiacDo As Date
Dim strFilterNaklady As String

datRokMesiacOd = DateSerial(Year(cmbStavOd), Month(cmbStavOd), 1)   'begin Date
datRokMesiacDo = DateSerial(Year(cmbStavK), Month(cmbStavK) + 1, 1) - 1 'end Date
strFilterNaklady = "[datNakladDatum] Between #" & datRokMesiacOd & "# And #" & datRokMesiacDo & "#"
With Me.frm_qryNaklady_subform.Form
    .Filter = strFilterNaklady
    .FilterOn = True
End With

For instance, result for strFilterNaklady looks like this: "[datNakladDatum] Between #1. 1. 2015# And #31. 12. 2015#". By debugging the code I receive an error message at statement:

With Me.frm_qryNaklady_subform.Form
.Filter = strFilterNaklady
.FilterOn = True
End With

Error 3709 - The search key was not found in any record.

But the underlying query contains tons of records fitting entered date range.

I'm playing with date filtering syntax all the day, but without success.

Do you see where I'm going wrong?

Gustav Gustav · Accepted Answer · 2015-06-12T11:53:05

You need proper date formatting:

strFilterNaklady = "[datNakladDatum] Between #" & Format(datRokMesiacOd, "yyyy\/mm\/dd") & "# And #" & Format(datRokMesiacDo, "yyyy\/mm\/dd") & "#"

Also, this can be reduced to:

datRokMesiacDo = DateSerial(Year(cmbStavK), Month(cmbStavK) + 1, 0) 'end Date

ACCESS 2007 - Form / subform filtering between dates

1 Answers