Others

How to Run an Access Query with Multiple Optional Combo Box Filters

Chanuka Geekiyanage Oct 9, 2026 869 views

Question details

The user needs to filter a database query using multiple combo boxes on a form, ensuring that if any combo box is left blank, the query does not exclude records due to Null values.

How to Run an Access Query with Multiple Optional Combo Box Filters

Product: Microsoft Access
Device & OS: not provided
Scenario: Searching and filtering database records using a custom form containing multiple optional selection boxes, such as brand, fuel type, and location.
Observed behavior: When multiple combo box filters are applied and one or more are left blank, the query incorrectly excludes records instead of ignoring the blank filters, returning fewer results than expected.

Before you start

Ensure you have the exact names of your target form, the combo box controls, and the corresponding fields in your Access table before modifying the query criteria.

Solution 1Recommended

Use the Nz() Function with Wildcard Criteria

Applying the Nz() function allows Access to treat empty combo boxes as empty strings, ensuring all records are returned when no specific filter is selected.

The most common issue with optional parameters in Access queries is that a blank combo box passes a 'Null' value to the query. By wrapping your form reference in an Nz() function and combining it with wildcard characters (*), the query will match all records if the combo box is left empty.

Open Query in Design View

Locate your query in the navigation pane, right-click it, and select 'Design View'.

Enter the Criteria

In the Criteria row for your desired field (e.g., Brand), enter the following expression: Like '*' & Nz([Forms]![YourFormName]![YourComboBoxName],'') & '*'

Handle Nulls in the Table Data

To prevent issues where the table's field itself contains Null values, change the Field row to: Nz([YourTableName].[FieldName],''). This ensures a match against the empty criteria.

Apply to Additional Fields

Repeat this exact pattern for any other optional combo boxes (like Fuel type or Location) using the 'AND' condition on the same Criteria row.

Use the Nz() Function with Wildcard Criteria

Test Thoroughly: After setting up the criteria, run the query with all combo boxes blank to ensure it returns the total number of expected records.

Solution 2

Dynamically Build the WHERE Clause using VBA

Write VBA code to construct a SQL query string that only includes filters for combo boxes that actually have a value selected.

Free Microsoft Office alternative

Looking for a Free, Lightweight Office Suite?

While WPS Office does not include a database management tool like Microsoft Access, it is an excellent, free alternative for your daily document, spreadsheet, and presentation needs. It offers a familiar interface and seamless compatibility with standard office file formats.

1. Visit the WPS Website: Go to the official WPS Office website to find the free installer.
2. Download the Software: Click the 'Free Download' button to get the setup file for your operating system.
3. Install and Use: Run the installer, follow the on-screen prompts, and instantly begin opening or editing your existing Office files.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats (.docx, .xlsx, .pptx).Lightweight design that runs smoothly on older devices without lagging.Familiar, tabbed user interface that makes transitioning from Microsoft Office effortless.Built-in PDF reader and editor for comprehensive document management.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access query return zero records when a combo box is empty?

When a combo box is left empty, Access treats its value as 'Null'. If your query criteria explicitly look for a value but encounter Null without a handling function like Nz(), Access filters out all records because no data matches 'Null'.

What does the Nz() function do in Microsoft Access?

The Nz() function checks a value and, if it is Null, converts it to a specified fallback value (such as an empty string or a zero). This prevents unexpected filtering results or calculation errors in your queries.

Can I use wildcards with number fields in Access queries?

Wildcards like the asterisk (*) are primarily designed for text strings. While Access often implicitly converts numbers to text when you use them with a 'Like' operator, doing so can cause performance issues or unexpected results compared to filtering text fields.

Office Quick Solutions