Others

Create an Access Combo Box to Filter a Report by Month and Year

Tauseeq MagsiTauseeq Magsi Nov 14, 2026 868 views

Question details

Create a drop-down list of months and years in MM/YYYY format and use the selection to filter an Access report.

Create an Access Combo Box to Filter a Report by Month and Year
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.
Before you start

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.

Solution 1Recommended

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.

1
Create a monthly calendar table

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.

2
Set up the combo box

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;

3
Add the query criterion

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.

4
Test the report

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.

Use a monthly calendar table and combo box to filter the report
Keep the list current: Add future months to MonthCalendar as needed. Months not present in the table will not appear in the combo box.
Free Microsoft Office alternative

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.

Review and filter exported report data in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX filesUse a familiar interface for an easier transition from OfficeWork with a free, lightweight office suite
microsoft office alternative - wps office

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.