How to Show All Records When an Access Query Combo Box is Null
Question details
The user needs a Microsoft Access query to filter records based on form combo box selections, but display all records instead of returning empty results when the combo box is left blank.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Building a dynamic search form in Access where users can filter query results using one or multiple combo boxes.
- Observed behavior
- By default, if a combo box criteria is referenced and the combo box is empty, the query returns no records. The goal is to return all records when the parameter is null.
Ensure you know the exact names of your Access Form and the corresponding Combo Box controls, as you will need to reference them accurately in the query design view.
Use the 'Or Is Null' Criteria in Query Design View
This method forces the query to evaluate the parameter dynamically, applying the filter if a value is selected, or ignoring the filter (showing all records) if the combo box is empty.
To make a query return all records when a combo box is blank, you need to use an OR logic statement in the Criteria row. This tells Access to either match the combo box value or check if the combo box is completely empty.
Right-click your target query in the Access Navigation Pane and select 'Design View'.
Find the column containing the field you want to filter using the combo box.
In the 'Criteria' row of that field, type the following formula, replacing 'frmLog' and 'cboTimeSort' with your actual form and combo box names: [Forms]![frmLog]![cboTimeSort] Or [Forms]![frmLog]![cboTimeSort] Is Null
If you have multiple combo boxes, repeat this process for each sortable field in their respective columns. Add the exact same logical pattern for each control.
Save the query and switch to Datasheet View to test the results. Leave the combo box empty to confirm all records are displayed.
Looking for a Lightweight Office Suite? Try WPS Office
While Microsoft Access is used for complex relational database management, most daily data tracking, analysis, and reporting can be done effortlessly in spreadsheets. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office, featuring a powerful Spreadsheet tool for all your data organization needs.
- 1. Download the Installer: Visit the official WPS website and download the free installer for your operating system.
- 2. Install WPS Office: Run the setup file and follow the quick on-screen instructions to install the suite.
- 3. Open WPS Spreadsheet: Launch WPS Spreadsheet to start organizing, filtering, and managing your datasets effortlessly.
Frequently Asked Questions
Why does Access rearrange my 'Or Is Null' criteria after saving?
Microsoft Access interprets the 'OR' logic and visually reformats it in the Query Design grid by placing the 'Is Null' condition on a separate 'or:' row and often duplicating the field name in a new column. This is just how the Access graphical interface translates complex SQL; it does not negatively affect the query.
Can I use a wildcard (*) instead of 'Is Null' for empty combo boxes?
Using a wildcard Like '*' works for text fields to show all records, but it fails if the field contains actual Null values in the database table itself. The '[Form] Or [Form] Is Null' method is much safer and works universally for all data types, including numbers and dates.
How do I handle multiple combo boxes filtering the same query?
You must apply the 'Or [Form]![Combo] Is Null' logic individually to the Criteria row of every field you wish to filter. When left on the same Criteria row in Design View, Access treats them as 'AND' conditions, meaning all active combo boxes will filter together, but any blank ones will simply be ignored.
Why does the query return no records when my combo box is empty without this criteria?
By default, Access treats an empty combo box as a literal Null value. If your query criteria just says '[Forms]![MyForm]![MyCombo]', it searches the database for records where the field is exactly equal to Null, which usually returns zero results unless the table records themselves are blank.
