Examples of Access query criteria
Query criteria help you zero in on specific items in an Access database. If an item matches all the criteria you enter, it appears in the query results.
To add criteria to an Access query, open the query in Design view and identify the fields (columns) you want to specify criteria for. If the field is not in the design grid, double-click the field to add it to the design grid and then enter the criterion in the Criteria row for that field. If you're not sure how to make this happen, see introduction to queries.
Here are some examples of commonly used criteria you can use as a starting point to create your criteria. The examples are grouped by data types.
In this article
Examples of criteria for Text fields
Let's say we have a contacts information table. The examples below show various criteria for the CountryRegion field in that query. The highlighted Criteria row is where you add the criterion.
| To include Items that... | Use this criterion | Query result returns items… |
| Exactly match a value, such as China | "China" | Where the CountryRegion field is set to China. For more see, applying criteria to text values. |
| Do not match a value, such as Mexico | Not "Mexico" | Where the CountryRegion field is set to a country/region other than Mexico. |
| Begin with the specified string, such as U | Like U* | For all countries/regions whose names start with "U", such as UK, or USA. Note: When used in an expression, the asterisk (*) represents any string of characters — it's also called a wildcard character. For a list of such characters, see the article Access wildcard character reference. |
| Do not begin with the specified string, such as U | Not Like U* | For all countries/regions whose names start with a character other than "U". |
| Contain the specified string, such as Korea | Like "*Korea*" | For all countries/regions that contain the string "Korea". |
| Doesn't have the specified string, such as Korea | Not Like "*Korea*" | For all countries/regions that do not contain the string "Korea". |
| End with the specified string, such as "ina" | Like "*ina" | For all countries/regions whose names end in "ina", such as China and Argentina. |
| Do not end with the specified string, such as "ina" | Not Like "*ina" | For all countries/regions that do not end in "ina", such as China and Argentina. |
| Contain null (or missing) values | Is Null | Where there is no value in the field. |
| Do not contain null values | Is Not Null | Where the value is not missing in the field. |
| Contain zero-length strings | "" (a pair of quotes) | Where the field is set to a blank (but not null) value. For example, items of sales made to another department might contain a blank value in the CountryRegion field. |
| Do not contain zero-length strings | Not "" | Where the CountryRegion field has a nonblank value. |
| Contains null values or zero-length strings | "" Or Is Null | Where there is either no value in the field, or the field is set to a blank value. |
| Is not empty or blank | Is Not Null And Not "" | Where the CountryRegion field has a nonblank, non-null value. |
| Follow a value, such as Mexico, when sorted in alphabetical order | >= "Mexico" | Of all countries/regions, beginning with Mexico and continuing through the end of the alphabet. |
| Fall within a specific range, such as A through D | Like "[A-D]*" | For countries/regions whose names start with the letters "A" through "D". |
| Match one of two values, such as USA or UK | "USA" Or "UK" | For USA and UK. |
| Contain one of the values in a list of values | In("France", "China", "Germany", "Japan") | For all countries/regions specified in the list. |
| Contain certain characters at a specific position in the field value | Right([CountryRegion], 1) = "y" | For all countries/regions where the last letter is "y". |
| Satisfy length requirements | Len([CountryRegion]) > 10 | For countries/regions whose name is more than 10 characters long. |
| Match a specific pattern | Like "Chi??" | For countries/regions, such as China and Chile, whose names are five characters long and the first three characters are "Chi". Note: When used in an expression, characters ? and _, represent a single character. The wildcard character _ cannot be used in the same expression with the ? character, nor can it be used with the asterisk character. |
See more information on applying criteria to text values.
Examples of criteria for Number and Currency fields
For this example, we have a UnitPrice query of a products table. Add the criteria to the criteria row and also the highlighted row if you have any OR criteria.
| To include items that... | Use this criterion | Query result returns items (records) where the unit price… |
| Exactly match a value, such as 100 | 100 | Of the product is $100. |
| Do not match a value, such as 1000 | Not 1000 | Of the product is not $1000. |
| Contain a value smaller than a value, such as 100 | < 100 | Is less than $100 (<100). The second expression (<=100) displays Items where the unit price is less than or equal to $100. |
| Contain a value larger than a value, such as 99.99 | >99.99 | Greater than $99.99 (>99.99). The second expression displays Items where the unit price is greater than or equal to $99.99. |
| Contain one of the two values, such as 20 or 25 | 20 or 25 | Is either $20 or $25. |
| Contain a value that falls with a range of values | >49.99 and <99.99 | Is between (but not including) $49.99 and $99.99. |
| Contain a value that falls outside a range | <50 or >100 | Is not between $50 and $100. |
| Contain one of many specific values | In(20, 25, 30) | Is $20, $25, or $30. |
| Contain a value that ends with the specified digits | Like "*4.99" | Ends with "4.99", such as $4.99, $14.99, $24.99, and so on. |
| Contain null (or missing) values | Is Null | Items where no value is entered in the UnitPrice field. |
| Contain non-null values | Is Not Null | Items where the value is not missing in the UnitPrice field. |
Examples of criteria for a Yes/No field
As an example, your Customers table has a Yes/No field named Active, used to indicate whether a customer's account is currently active. The following table shows how values entered in the Criteria row for a Yes/No field are evaluated.
| Field value | Result |
| Yes, True, 1, or -1 | Tested for a Yes value. A value of 1 or -1 is converted to "True" in the Criteria row after you enter it. |
| No, False, or 0 | Tested for a No value. A value of 0 is converted to "False" in the Criteria row after you enter it. |
| No value (null) | Not tested |
| Any number other than 1, -1, or 0 | No results if it's the only criteria value in the field |
| Any character string other than Yes, No, True, or False | Query fails to run due to Data type mismatch error |
Date/Time criteria
There are many ways to use Date/Time criteria in a query. See the article Using dates as criteria in Access queries.
No comments:
Post a Comment