Fix Access Query IS NULL Criteria Not Returning Blank Fields
Question details
The user needs a way to configure an MS Access query with multiple optional form filters so that it correctly returns records containing Null or zero-length values when the filter parameters are left blank.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Filtering records in an Access database using optional form parameters alongside LIKE operators.
- Observed behavior
- The query fails to return records containing Null or zero-length values when the optional form controls are not filled out, effectively filtering out valid empty records.
Before modifying your database queries, ensure you have a backup of your Access database. Open your Access form and verify the exact names of the controls you are using as optional filters.
Group Criteria with IS NULL Conditions in SQL View
Explicitly handle NULL values for both the form control and the table field by grouping them with OR statements inside your WHERE clause.
When using the LIKE operator, Access excludes NULL values by default. To bypass this, you must explicitly tell the query to return the record if the form parameter is empty or if the field itself is NULL.
Right-click your query in the navigation pane and select 'Design View'. Then, switch to 'SQL View' using the View button in the top left corner.
Modify your criteria to wrap the LIKE search and the IS NULL checks in parentheses. Use this format: WHERE (Customer LIKE "*" & [Forms]![SearchForm]![Customer] & "*" OR [Forms]![SearchForm]![Customer] IS NULL OR Customer IS NULL).
Repeat this grouped pattern for every other searchable field, separating them with the AND operator. For example: AND (City LIKE "*" & [Forms]![SearchForm]![City] & "*" OR [Forms]![SearchForm]![City] IS NULL OR City IS NULL).
Append a Zero-Length String to the Field
Concatenate an empty string to the field in your criteria to force Access to treat NULLs as zero-length strings during the LIKE comparison.
Build Filter Dynamically with VBA
For complex forms with many searchable fields (e.g., 10+ fields), construct the WHERE clause dynamically using VBA to avoid overly complex and slow SQL statements.
Looking for a Lightweight Alternative to Microsoft Office?
While WPS Office does not include a database management tool like Microsoft Access, it provides powerful, free, and highly compatible alternatives to Word, Excel, and PowerPoint. If you are handling large datasets exported from a database, WPS Spreadsheet offers advanced filtering, pivot tables, and data analysis tools without the steep price tag.
- 1. Visit the Official Website: Go to the official WPS Office website to find the free download link.
- 2. Download and Install: Download the lightweight installer and follow the on-screen instructions to set it up on your device.
- 3. Open WPS Spreadsheet: Launch WPS Spreadsheet to seamlessly import, analyze, and filter your database exports.
Frequently Asked Questions
Why does the LIKE operator ignore NULL values in Access?
In SQL, comparing any value to NULL using standard operators (like LIKE or =) results in 'Unknown', not 'True'. Because it does not evaluate to True, records with NULL values are automatically filtered out unless explicitly handled with the 'IS NULL' criteria.
What is the difference between a NULL value and a zero-length string in Access?
A NULL value indicates the total absence of data (it is unknown), whereas a zero-length string ("") is a text string that contains no characters (it is known to be empty). Your query criteria must handle both scenarios if your table fields allow them.
Can I use cascading combo boxes instead of a massive WHERE clause?
Yes. Cascading combo boxes allow each selection to filter the options of the subsequent combo box. This progressively narrows down the dataset and heavily reduces the need for complex, multi-field SQL LIKE criteria.
