Create an Access Combo Box to Filter a Report by Month and Year
Question details
Create a drop-down list of months and years in MM/YYYY format and use the selection to filter an Access report.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A user wants to choose a month and year from a combo box before opening a report.
- Observed behavior
- Formatting a date directly as a query criterion did not return the expected results.
Identify the report's record source and its date field, such as OrderDate. Create or identify a form that will remain open while the report runs.
Use a monthly calendar table and combo box to filter the report
Use this approach to offer a chronological list of month-and-year choices and pass the selected text to the report query.
The combo box returns a formatted text value such as 03/2025. The query must compare it with the report's date field formatted in the same way.
In Access, create a table named MonthCalendar with a Date/Time field named calDate. Enter the first day of each month you want users to select.
Open your form in Design View and add a combo box named cboYearMonth. In its Property Sheet, set Row Source Type to Table/Query and Row Source to: SELECT Format(calDate,"mm/yyyy") AS MonthYear FROM MonthCalendar ORDER BY calDate;
Open the report's record source in Query Design. Add a calculated field such as ReportMonth: Format([OrderDate],"mm/yyyy") and enter Forms!frmYearMonthDlg!cboYearMonth in its Criteria row. Replace the form, control, and date field names with your own.
Open the form in Form View, select a month from cboYearMonth, and open the report while the form remains open. Confirm that the report contains records from the selected month.

Review exported report data in WPS Spreadsheets
WPS Office is a free, lightweight Office alternative with a familiar interface and support for common Microsoft Office formats, making it easy to work with exported data. WPS does not replace the Access form, query, or report filter described above.

Frequently Asked Questions
Why didn't my formatted date criterion return results?
The combo box value and the formatted query expression must match. In Query Design, compare Format([OrderDate],"mm/yyyy") with Forms!frmYearMonthDlg!cboYearMonth, using your actual field and control names.
Does the selection form need to stay open?
Yes. The query reference Forms!frmYearMonthDlg!cboYearMonth reads the value from the open form, so select a month and keep that form open when you run the report.
How do I add more months to the drop-down list?
Open MonthCalendar and add a calDate record for the first day of each additional month. The combo box row source sorts those dates chronologically.
Can WPS Office run the Access combo box and report?
No. Create and run the combo box, query, and report in Microsoft Access. You can export report data to an Excel file and review it in WPS Spreadsheets.




