Others

How to Filter an Access Form Using Multiple Optional Criteria

WPS Editor Oct 9, 2026 869 views

Question details

The user needs to filter records in a Microsoft Access form using multiple combo boxes and a checkbox, ensuring that empty combo boxes are ignored and the checkbox triggers a specific condition (such as quantity being greater than zero).

How to Filter an Access Form Using Multiple Optional Criteria

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating a dynamic search or filter form in Access where users can leave some control criteria blank to view all records, or check a box to apply a specific numerical threshold.
Observed behavior: Setting up query criteria accurately to handle both Null values from unselected combo boxes and Boolean states from checkboxes without breaking the underlying query filter logic.

Before you start

Ensure you have noted down the exact names of your form, combo boxes, checkboxes, and underlying query fields before modifying the SQL criteria.

Solution 1Recommended

Use Optional Criteria in the Underlying Form Query

The most robust way to filter an Access form is by modifying its record source query to account for both populated and null control values.

By leveraging the 'OR IS NULL' logic in your query design, Access will apply the filter if the user selects a value in the combo box, but will return all records for that field if the combo box is left empty.

Similarly, for boolean check boxes, testing for False rather than Null ensures consistent behavior across different form states.

Open the form's query in Design View

Identify the query that acts as the Record Source for your form, right-click it, and select Design View.

Apply criteria for Combo Boxes

In the Criteria row for your desired field, enter the following pattern: (Field1 = Forms!FormName!Combo1 OR Forms!FormName!Combo1 IS NULL). Repeat this for each field tied to a combo box.

Apply criteria for the Checkbox

For a field that requires a threshold condition (like QtyInStock > 0), use: ((QtyInStock > 0 AND Forms!FormName!CheckBoxName = True) OR Forms!FormName!CheckBoxName = False).

Configure Checkbox properties

Open the Property Sheet for your Checkbox on the form. Set the 'Default Value' to False and ensure 'Triple State' is set to No.

Add Me.Requery to Form Controls

Open the VBA editor for your form. Add the command 'Me.Requery' to the AfterUpdate event of every combo box and checkbox so the form instantly updates when the user changes a filter.

Use Optional Criteria in the Underlying Form Query

Update Placeholder Names: Be sure to replace 'FormName', 'Combo1', 'CheckBoxName', and 'Field1' with the actual object and field names used in your database.

Solution 2

Apply DLookup for External Calculated Values

If the quantity or criteria you want to filter against is stored in a separate query or table, use DLookup to pull it into your main query.

Free Microsoft Office alternative

Need a Lightweight Alternative for Office Data Management? Try WPS Office

While Microsoft Access is designed for complex relational database management, many daily data tracking and filtering tasks can be handled efficiently using advanced spreadsheet functions. WPS Office provides a free, highly compatible, and intuitive suite to manage your data without complex VBA coding.

High compatibility with Microsoft Excel (.xlsx), Word, and PowerPoint formats.Powerful data filtering, slicers, and pivot table features in WPS Spreadsheet for easy data management.Lightweight, fast installation with a user-friendly tabbed interface.Completely free core features to significantly reduce your software costs.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access form filter fail when a combo box is left empty?

If your query criteria uses standard equals (=) without accounting for nulls, an empty combo box passes a Null value. The query tries to find records that literally equal 'Null', returning zero results. Adding the 'OR [ControlName] IS NULL' logic fixes this.

What does Me.Requery do in Microsoft Access?

Me.Requery is a VBA command that tells the currently active form to re-run its underlying query. This forces the form to refresh the displayed records based on the newly updated criteria in your form controls.

Can I use a command button to apply filters instead of the AfterUpdate event?

Yes. Instead of putting Me.Requery in every single control's AfterUpdate event, you can add an 'Apply Filter' button to your form and place the Me.Requery command entirely within its OnClick event.

How do I handle multiple checkboxes for filtering?

You can chain the criteria for each checkbox using the AND operator in your query's Design View. Just apply the same ((Field > X AND Checkbox = True) OR Checkbox = False) logic within the specific column for each distinct field you are checking.

Office Quick Solutions