If an Access query returns no records, first run it without criteria to confirm that its source contains data. Then restore filters one at a time, check field types and parameter values, and inspect joins. This sequence helps pinpoint what is excluding the rows without changing the underlying data.
1. Confirm the source has records to return
Open the table or source query and check the fields your query uses. Then run the select query with its criteria removed. Microsoft explains that Access compares criteria expressions with field values to determine which records appear (Microsoft Support: Examples of query criteria).
As an Amazon Associate I earn from qualifying purchases.
- If the unfiltered query is also empty, investigate its source table or query and any joins before focusing on criteria.
- If rows appear, the source has candidate records; proceed to identify which filter or join removes them.
2. Find criteria that exclude the records
Open the query in Design view and inspect the Criteria and Or rows for each field. Conditions on the same Criteria row are combined: a record must satisfy all of them. Conditions on separate Or rows provide alternative ways for a record to qualify. A criterion in the wrong field, an unintended operator, or an extra space can prevent an expected match.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11- Remove all criteria and run the query to establish a baseline.
- Add one criterion back and run the query.
- Continue one condition at a time until the results disappear. The last condition added is the first place to investigate.
Check both the field selected in the design grid and the exact expression. Microsoft’s criteria examples show how expressions are used to match field values (Microsoft Support: Examples of query criteria).
#1 Best Overall
3. Match the criterion to the field type and stored value
A value that looks right on screen may be stored differently or belong to a different data type than the criterion assumes. Check the field definition and inspect a few actual values in the source. Access criteria differ for text, numbers, dates, Yes/No fields, and Null values.
- Missing values: To find records with no value in a field, use
Is Null; an empty-looking value is not necessarily the same thing as Null. - Yes/No fields: Use an appropriate Boolean value, such as
Yes/TrueorNo/False, rather than text that merely resembles one. - Dates and numbers: Confirm that the stored value and comparison use the intended type and value, not a text representation that only looks equivalent.
- Text: Check spelling, spaces, and punctuation against the actual stored text.
Microsoft documents criteria examples across field types in its query criteria guide.
Rank #2
4. Check parameter prompts and data types
If the query asks for a value, verify that the parameter name matches the reference used in the criterion and that the prompt is intentional. A misspelled or unintended reference can cause Access to ask for input you did not expect. Temporarily replace the parameter with a known value that should match a record; if results appear, investigate the prompt text or entered value.
Set parameter data types where appropriate, especially for number, currency, and date/time inputs. Microsoft explains how to declare parameters and assign data types in Microsoft Learn: PARAMETERS declaration.
Rank #3
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
5. Verify partial-text search syntax
If the query should find text containing a word or fragment, a whole-value comparison will not do. Check that the criterion uses Like and the wildcard convention supported by your database. Microsoft gives this parameter pattern as an example:
Like "*" & [parameter] & "*"
That pattern searches for the entered text within a field value. Wildcard characters and their meanings are documented in Microsoft Support: Access wildcard character reference. Check the wildcard style used by your database rather than assuming every Access setup uses the same convention.
Rank #4
6. Check whether a join removes unmatched records
Queries that use more than one table can return no rows because their join requires matching values. Access may create an inner join automatically; an inner join returns pairs of records with matching values, so a row without a match on the other side is omitted. Review the join line in Design view, confirm that the joined fields contain corresponding values, and check that their data types are compatible.
Free tools Windows power users keep installed
One-click scans. No signup required.
- Temporarily remove the join or the other table and run the query to see whether the first table has candidate records.
- Check the joined key values in both sources for differences that would prevent a match.
- If unmatched records should remain, change the join to the appropriate left or right outer join. Choose the direction based on which table’s records you need to retain.
Microsoft explains how Access joins tables in Join tables and queries and describes join types in Microsoft Learn: INNER JOIN operation.
Best Value
Which check should you try first?
| Possible cause | First check | Diagnostic test |
|---|---|---|
| Criteria are too restrictive or applied to the wrong field | Criteria and Or rows in Design view | Remove criteria, then add one condition at a time |
| Stored value or field type differs from the assumption | Field definition and sample values in the source | Try a simple criterion appropriate to that field type |
| Parameter name, type, or input does not match | Parameter reference and entered value | Temporarily use a known test value |
| Partial-text pattern does not match | Like expression and wildcard style |
Test a known substring with the appropriate wildcard pattern |
| A join excludes unmatched rows | Join type, fields, and actual key values | Remove the join temporarily or use an outer join if unmatched rows should remain |
If the query is still empty
To narrow down a query that remains empty, gather details that make its behavior reproducible:
- The SQL or a screenshot of the query’s Design view.
- Relevant field names and data types, plus a few anonymized sample values.
- Any parameter values entered when prompted.
- Whether rows appear when criteria or joins are removed.
Without the query, schema, sample values, and parameter inputs, there is not enough information to identify a specific cause. Interface labels can also vary somewhat by Access version.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
