Data range selection does not work properly with year and month filter from combobox in ms access fo-CodePudding

I have a form in MS Access where I can filter dates using the date range ( "from:" and "to:" as combobox, they are called in code as "cboStart" and "cboEnd"). When you click on the Combobox, you will see 02.2021, 05.2021, 06.2021, 01.2022, 02.2022, 03.2022 etc. They show all options that exist (it's just showing the values that exist in the query, I'm calling the dates from the query)

Code is (I found this in youtube):

Private Sub cboEnd_AfterUpdate()

UpdateFilter
Me.Requery
End Sub


Private Sub cboStart_AfterUpdate()

UpdateFilter
Me.Requery
End Sub

Sub 

UpdateFilter()
Dim strFilter As String
strFilter = FilterString()
If Len(strFilter) > 0 Then
  Me.Filter = strFilter
  Me.FilterOn = True
Else
  Me.Filter = ""
  Me.FilterOn = False
End If
End Sub

Function FilterString() As String
Dim strReturn As String
Dim strStart As String
Dim strEnd As String
strReturn = ""
If IsNull(Me!cboStart) Or IsNull(Me!cboEnd) Then GoTo Fn_Exit
strStart = Me!cboStart
strEnd = Me!cboEnd
strReturn = "Month_Worked Between '" & strStart & "' And '" & strEnd & "'"
Fn_Exit:
FilterString = strReturn
End Function

But I don't want to show all year and months in combobox so I added new comboboxes (they are called in code as "cmb_YearFrom" and "cmb_YearTo" for Year so I filter months by year selection.

to do this I wrote a code and added in a query [Forms]![frmOffeneBestellung]![cmb_YearFrom], [Forms]![frmOffeneBestellung]![cmb_YearTo], so comboboxes "year" are used for month selection as a filter

code:

Private Sub cmb_YearTo_AfterUpdate()
cboEnd = Null
cboEnd.Requery
Me.Requery
End Sub

Private Sub cmb_YearFrom_AfterUpdate()
cboStart = Null
cboStart.Requery
Me.Requery
End Sub

Now it's like this: "Year:", "From:" "Year:","To:"

My Problem: For example I can't filter from 06.2021 to 01.2022. It only works in the same year like from 05.2021 to 06.2021. How can I fix it, any ideas? Thanks a lot in advance

CodePudding user response：

You must convert those text dates to true dates and date expressions, for example:

strReturn = "CDate(Replace([Month_Worked], '.', '/')) Between #" & Format(Replace(strStart, ".", "/"), "yyyy\/mm\/dd") & "# And #" & Format(Replace(strEnd, ".", "/"), "yyyy\/mm\/dd") & "#"