Two combo box start month and year and end month and year, to filter a report from a form

[CraigDouglas]

Hi, please help if you can. I have a form frmSwitchBoard on it I have two combo boxes cboMonthYearStart and the other cboMonthYearEnd. I have a button that opens up a report rptOrderFromStartMonthYearToEndMonthYear and I want it to filter the start month and year chosen in the first combo box to the end month and year chosen in the second box. The field I use to get my combo box info is called MonthAndYear. I would like the vba code to show all entries which are group as month and year if nothing is chosen in the combo box and I would like the report to filter the records from the chosen month year to the end month year that gets chosen. The code I have so far is:

Private Sub Report_Open(Cancel As Integer)

On Error GoTo ErrorHandler
'Add filter
Dim frm As Form
Set frm = Forms!frmSwitchBoard 'Must Be Open
Dim strFilter As String
strFilter = ""
'If both are empty show everything
If Len("" & frm!cboMonthYearStart) = 0 And Len("" & frm!cboMonthYearEnd) = 0 Then
Me.Filter = ""
Me.FilterOn = False
Exit Sub
End If

From frm!cboMonthYearStart To frm!cboMonthYearEnd

End If
'Add filter
Me.Filter = strFilter
Me.FilterOn = True

CleanUpAndExit:

Exit Sub

ErrorHandler:
Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")

Resume CleanUpAndExit

End Sub

 

[theDBguy]

Hi. What is the data type of the field you want to filter?

 

[CraigDouglas]

It is a date field I believe is the answer.

 

[CraigDouglas]

I formatted the date to month and year to get my combo boxes.

 

[theDBguy]

I formatted the date to month and year to get my combo boxes.

What is the Row Source for your Comboboxes?

 

[CraigDouglas]

Month year start SELECT DISTINCT qryOrderByMonthAndYear.MonthAndYear FROM qryOrderByMonthAndYear;

Month year end SELECT DISTINCT qryOrderByMonthAndYear.MonthAndYear FROM qryOrderByMonthAndYear;

 

[CraigDouglas]

Please, I am trying to get the choices in the combo boxes to work from the first choice to the second choice so if I chose 02 2022 and 05 2022 it will include those months. Craig

 

[arnelgp]

here is a simple demo.

 

[CraigDouglas]

here is a simple demo.

Thank you. I will see if I can figure it out.

 

[CraigDouglas]

arnelgp I thank you for the database. I could not get my code to work. Please would you have a look at my database and see if you can get it to work? I used the qryOrderByMonthAndYear to populate my combo boxes. They are on the switchboard form. Under the heading Choose from start month year to end month year. I put the code on the open event of the report rptOrdersFromStartMonthYearToEndMonthYear. I see your row source code in your combo boxes is different to mine. Craig

 

[CraigDouglas]

here is a simple demo.

Hi, arnelgp. I sent you a message asking if you could help with my database. Craig

 

[arnelgp]

i just seen your post.

 

[arnelgp]

i added another column to your query.
also added Validation to your 2 buttons.

 

[CraigDouglas]

i added another column to your query.
also added Validation to your 2 buttons.

Thank you so much for going out of your way for me. What does the following mean? Expr1 >= '201912' And Expr1 <= '203003' Craig

 

[arnelgp]

see your query, i added Expr1.