Others

Fix Access Query Returns No Results When Fields Are Null

Bushra Parveen Sep 27, 2026 868 views

Question details

The user needs to modify a wildcard search query so that it successfully returns records even when some of the searched fields contain Null (empty) values.

Fix Access Query Returns No Results When Fields Are Null

Product: Microsoft Access
Device & OS: not provided
Scenario: Running a multi-field search query using a wildcard criterion tied to a form input box.
Observed behavior: The query fails to return any records if one or more fields in the dataset contain a Null value, instead of filtering normally.

Before you start

Before modifying your query criteria, verify the exact names of your form controls and ensure you are familiar with Access Design View.

Solution 1Recommended

Append the "Or Is Null" Criterion to Your Wildcard Search

Use the built-in Access "Is Null" operator alongside your wildcard search to ensure blank records are not filtered out.

When you use wildcard searches like `Like "*"` in Access, it explicitly looks for string data. Because Null represents an absence of data, the wildcard condition evaluates to false, causing the query to hide the entire record. To fix this, you must explicitly tell the query to also accept Null values.

Do not use the text string "isnull" as a criterion, as Access will interpret it as literal text rather than the database state of being null.

Open Query in Design View

Open your Microsoft Access database, right-click the problematic query in the Navigation Pane, and select "Design View".

Locate the Criteria Field

Find the column containing your wildcard criteria (e.g., the field referencing your search form input).

Add the Operator

Update the criteria to include `Or Is Null` at the end of the expression. For example, change it to: `Like "*" & [Forms]![frm_Navigation]![NavigationSubform].[Form]![txt_Payee_Search] & "*" Or Is Null`.

Run and Test

Click the "Run" button in the Ribbon to verify that records containing empty values in that specific field are now correctly displayed.

Append the "Or Is Null" Criterion to Your Wildcard Search

Handling Multiple Fields: If you are applying this logic across multiple fields, ensure you properly group your OR conditions using parentheses in the SQL view to prevent cross-field logical conflicts.

Free Microsoft Office alternative

Manage Exported Database Reports with WPS Office

While Microsoft Access handles your complex database management, you often need to export query results for analysis, charting, and sharing. WPS Office provides a lightweight, highly compatible spreadsheet solution to handle your exported data flawlessly, without the high subscription costs.

1. Export Data from Access: Run your fixed query in Microsoft Access and export the results as an Excel or CSV file.
2. Open with WPS Spreadsheet: Launch WPS Office and open the exported file to view your data.
3. Analyze and Share: Use WPS Spreadsheet's built-in formulas and charts to analyze the query results and share them with your team.

Seamless compatibility with Microsoft Excel formats (.xlsx, .csv)Advanced pivot tables and data filtering for query analysisFamiliar user interface with zero learning curveCompletely free and lightweight Office alternative

microsoft office alternative - wps office

Frequently Asked Questions

Why does my wildcard search ignore fields that are blank?

In relational databases like Microsoft Access, "blank" often means the field is Null (an unknown value). Standard string matching operators like wildcards cannot evaluate Null, causing the record to be excluded from the results.

Can I use the Nz() function to handle Null fields in my query?

Yes. You can wrap your field name in the Nz() function, such as `Nz([Field_Name], "") Like "*" & [SearchTerm] & "*"`. This temporarily converts the Null value to an empty string so the wildcard search can process it, though using "Or Is Null" is often more efficient for database indexing.

What is the difference between "Is Null" and typing "isnull" in the criteria?

"Is Null" is the proper SQL operator to check if a database field contains no data. Typing "isnull" without spaces causes Access to search for the literal text word "isnull", which will fail to find actual blank fields.

Office Quick Solutions