VBA & Macro Problems

How to Reference an Access Combo Box in a Macro Filter

Natalie Taylor Oct 16, 2026 869 views

Question details

The user needs to filter records in a Microsoft Access database based on the specific year and month selected from a form's combo box.

How to Reference an Access Combo Box in a Macro Filter

Product: Microsoft Access
Device & OS: not provided
Scenario: Applying a macro or VBA filter to a form so that displayed records match the exact date period chosen in a combo box, while excluding records outside of this range.
Observed behavior: The standard filter method may fail to isolate records by both month and year properly, especially when excluding records that fall after the selected period.

Before you start

Ensure your form is correctly bound to your data source (a table is often easier to filter than a complex query in this scenario) and verify that the combo box returns a valid date value rather than a text string.

Solution 1Recommended

Use a SearchForRecord Macro Action with a WHERE Condition

Separate the year and month comparisons in the WHERE condition of your macro to reliably filter the data.

When filtering by a specific period using a macro, each comparison within the WHERE condition must be written separately. This guarantees that Access correctly evaluates both the year and the month from your combo box.

Open your Access Form in Design View

Right-click your form in the navigation pane and select 'Design View'.

Add a SearchForRecord Action

Create a macro for the 'After Update' event of your combo box. In the Macro Builder, add the 'SearchForRecord' action.

Define the WHERE Condition

Enter the following condition: Year([PERIOD])=Year(Forms![Form1]![PERIOD_SELECT_COMBO]) AND Month([PERIOD])=Month(Forms![Form1]![PERIOD_SELECT_COMBO]). Replace 'Form1' with your actual form name and 'PERIOD' with your table's date field.

Save and Test

Save the macro, switch to Form View, and select a date from the combo box to verify that it jumps to or filters the correct first matching record.

Use a SearchForRecord Macro Action with a WHERE Condition

Syntax check: Ensure your field names and control names are enclosed in brackets [ ] to prevent syntax errors during execution.

Solution 2

Implement a VBA Filter using DateSerial

Use VBA code to apply an advanced date range filter that explicitly defines the start and end of the selected month.

Free Microsoft Office alternative

Looking for a Free, Lightweight Office Suite?

While WPS Office does not include a direct database program like Microsoft Access, it offers exceptional, free alternatives to Excel, Word, and PowerPoint. If you are handling data in spreadsheets instead of databases, WPS Spreadsheet provides powerful filtering, VBA support, and seamless format compatibility.

Completely free and lightweight office suiteHigh compatibility with Microsoft Excel (.xlsx), Word, and PowerPoint formatsSupports advanced spreadsheet data filtering and macro functionsFamiliar tabbed interface for a seamless transition

microsoft office alternative - wps office

Frequently Asked Questions

Why is my macro filter returning a 'Data Type Mismatch' error?

This error typically occurs if the combo box returns a text string instead of a valid date value. Check the RowSource property of your combo box and ensure that the Bound Column contains actual date/time values rather than formatted text.

How does the DateSerial function work in VBA filters?

DateSerial(year, month, day) creates a valid date from individual year, month, and day integers. In filtering, it allows you to dynamically build the exact first day (day 1) and last day (day 0 of the following month) of the selected period to construct precise date ranges.

Does my form need to be based on a table or a query to filter records?

While forms can be based on both tables and queries, complex filters sometimes behave unpredictably on heavily aggregated queries. Changing the form's Record Source to a direct table often simplifies troubleshooting when applying dynamic date filters.

Excel & Data Problems