Open the query in Design view, add the field you want to filter if it is not already on the design grid, and type a criterion in that field’s Criteria row. Run the query to see which records match. A criterion is an expression Access compares with field values to decide whether to include each record.
Add a criterion in Query Design
-
In the Navigation Pane, right-click the saved query and choose Design View.
As an Amazon Associate I earn from qualifying purchases.
-
Find the field whose values should determine which records appear. If it is not in the design grid, double-click it in the field list or drag it into an empty grid column. You can use a field for filtering even if you do not want it displayed in the results: clear its Show checkbox.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
In the grid’s Criteria row beneath that field, enter an expression suited to the field’s data type.
#1 Best Overall
-
Select Run (the red exclamation-mark button) to view the matching records. Return to Design view to adjust the criterion.
Microsoft documents this workflow for Access for Microsoft 365 and Access 2024, 2021, 2019, and 2016. Microsoft’s query-criteria examples explain the expression syntax and grid behavior.
Choose syntax that fits the field
These are sample expressions to type in a Criteria cell. Replace the sample values with values present in your data; text values are commonly enclosed in quotation marks, and date literals in the examples use number signs.
Rank #2
| What you want | Example criterion | Effect |
|---|---|---|
| Match exact text | ="Chicago" |
Returns records whose text field is Chicago. |
| Match text beginning with U | Like "U*" |
Matches text starting with U in the familiar Access ANSI-89 wildcard syntax. |
| Find text containing Korea | Like "*Korea*" |
Matches Korea anywhere in the field. |
| Match one of several text values | In("France", "China", "Germany") |
Returns values included in the list. |
| Match numbers above 25 and below 50 | >25 And <50 |
Excludes both endpoints. |
| Match numbers from 50 through 100 | Between 50 And 100 |
Includes both endpoints. |
| Find records with or without a value | Is Null / Is Not Null |
Tests whether the field has no value or has a value. |
| Match a particular date | #2/2/2012# |
Matches the example date using Access’s documented date delimiters. |
| Match dates in an interval | Between #1/1/2017# And #3/31/2017# |
Matches dates in the stated range, including the endpoints. |
| Use a date relative to today | Date() or DateAdd(...) |
Uses a date function to make the criterion dynamic; choose the expression that represents the period you need. |
For further examples of text criteria, see Microsoft’s guidance on text values; for date expressions, see its date-criteria examples.
Combine conditions with AND and OR
Criteria entered on the same grid row across different fields are combined with AND: every condition must be true for a record to appear. For example, putting Chicago under City and a birth-date condition under BirthDate on the same row requires a record to satisfy both.
To allow alternatives, put one condition in the Criteria row and the alternative in the Or row (or another lower alternate row). Those rows are combined with OR, so a record can match either condition. Putting a second condition under another field on the same row does not make it an alternative.
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
Use the wildcard syntax that matches the database
In the familiar ANSI-89 Access pattern syntax, * represents zero or more characters and ? represents one character. Bracket expressions can match a character set, such as [ae], or a range, such as [a-h]. For example, Like "wh*" can match “wh,” “what,” “white,” and “why.”
Free tools Windows power users keep installed
One-click scans. No signup required.
ANSI-92 databases use a different wildcard family, including % and _ in place of * and ?. If a pattern does not behave as expected, check the database’s ANSI-89/ANSI-92 setting and use the corresponding characters. See Microsoft’s Access wildcard reference.
Choose between a fixed criterion and a parameter
A fixed criterion suits a saved query when the field and value stay the same. If the field stays constant but the value changes between runs, use a parameter prompt such as [Enter a city:]. When the query runs, Access asks for the value and uses it as the criterion. A parameter can also be combined with Like for partial matching.
Rank #4
For date, number, and currency parameters, declare the parameter’s data type so Access handles the entered value appropriately. Microsoft explains parameter prompts and data-type settings in its parameter-query guidance.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Troubleshoot criteria that return unexpected results
-
No records appear: The query may be working correctly but no stored values match. Check that you chose the intended field and that the value is present in the data. Microsoft notes that zero matches can simply mean no records satisfy the criterion. See the query-criteria examples.
Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
A text match fails: Check the spelling and quotation marks, and confirm the field actually contains the text you expect.
Best Value
-
A date match fails: Use the documented
#date delimiters and check that the entered date corresponds to the stored date. Database settings can affect literal syntax; Microsoft notes an ANSI-92 caveat in its date-criteria guidance. -
A wildcard match fails: Confirm whether the database uses ANSI-89 or ANSI-92 syntax, then use the matching wildcard characters.
-
Too few records match: Check whether conditions meant as alternatives were accidentally placed on the same row, where Access combines them with AND.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsSpecial offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.Quick Recap
Bestseller No. 1SaleBestseller No. 2SaleBestseller No. 3
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.
