Two combo box start month and year and end month and year, to filter a report from a form (1 Viewer)

CraigDouglas

Registered User.
Local time
Today, 19:36
Joined
Sep 14, 2016
Messages
31
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:

Code:
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

I’m here to help
Staff member
Local time
Today, 12:36
Joined
Oct 29, 2018
Messages
21,358
Hi. What is the data type of the field you want to filter?
 

CraigDouglas

Registered User.
Local time
Today, 19:36
Joined
Sep 14, 2016
Messages
31
It is a date field I believe is the answer.
 

CraigDouglas

Registered User.
Local time
Today, 19:36
Joined
Sep 14, 2016
Messages
31
I formatted the date to month and year to get my combo boxes.
 

CraigDouglas

Registered User.
Local time
Today, 19:36
Joined
Sep 14, 2016
Messages
31
Month year start SELECT DISTINCT qryOrderByMonthAndYear.MonthAndYear FROM qryOrderByMonthAndYear;

Month year end SELECT DISTINCT qryOrderByMonthAndYear.MonthAndYear FROM qryOrderByMonthAndYear;
 

CraigDouglas

Registered User.
Local time
Today, 19:36
Joined
Sep 14, 2016
Messages
31
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:36
Joined
May 7, 2009
Messages
19,169
here is a simple demo.
 

Attachments

  • monthYear.accdb
    768 KB · Views: 256

CraigDouglas

Registered User.
Local time
Today, 19:36
Joined
Sep 14, 2016
Messages
31
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
 

Attachments

  • Sales21-22FullAddress.accdb
    2 MB · Views: 234

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:36
Joined
May 7, 2009
Messages
19,169
i just seen your post.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:36
Joined
May 7, 2009
Messages
19,169
i added another column to your query.
also added Validation to your 2 buttons.
 

Attachments

  • Sales21-22FullAddress.accdb
    1.7 MB · Views: 261

CraigDouglas

Registered User.
Local time
Today, 19:36
Joined
Sep 14, 2016
Messages
31
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
 

Users who are viewing this thread

Top Bottom