Advertisements

Examples of query criteria

Access for Microsoft 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007

Query criteria make it easier for you to hone your sights on specific items in an Access database. If an item matches all the criteria you enter, it appears in the query results.

To create criteria to an Access query, open the query in Design view and figure out the fields (columns) you want to specify criteria for. If the field is not in the design grid, double-click the field to append it to the design grid and then type the criterion in the Criteria row for that field. If you’re puzzled about how to make this happen, see Introduction to queries.

A query criterion refers to an expression that Access compares to query field values to conclude whether to consider the record that contains each value. For example, = “Chicago” is an expression that Access can compare to values in a text field in a query. If the value for that field in a given record is “Chicago”, Access takes this record into account in the query results.

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 topic

Introduction to query criteria

Criteria for Text, Memo, and Hyperlink fields

Criteria for Number, Currency, and AutoNumber fields

Criteria for Date/Time fields

Criteria for Yes/No fields

Criteria for other fields

Introduction to query criteria

A criterion bears some commonalities to a formula — it is a string that could comprise field references, operators, and constants. Query criteria too are collectively known as expressions in Access.

The table underneath lists some sample criteria and elucidates how they work.

CriteriaDescription
>25 and <50This criterion applies to a Number field, such as Price or UnitsInStock. It includes only those records where the Price or UnitsInStock field contains a value greater than 25 and less than 50.
DateDiff (“yyyy”, [BirthDate], Date()) > 30This criterion applies to a Date/Time field, such as BirthDate. Only records where the number of years between a person’s birthdate and today’s date is greater than 30 are included in the query result.
Is NullThis criterion can be applied to any type of field to show records where the field value is null.

Evidently, it becomes apparent that criteria can appear uniquely from one another, based on the data type of the field that they correspond to and your personal specifications. Some criteria are basic, and employ standard operators and constants. Whereas, others are more advanced, and rely on functions, special operators, and contain field references.

This topic specifies multiple frequently used criteria by data type. It may be helpful for you to customise your personal criteria should the examples referred to in this topic are irrelevant to your exact needs. Doing this requires you to gain a good grounding of the complete list of functions, operators, special characters, and the syntax for expressions relating to fields and literals.

From there, you will observe the location and steps taken to apply the criteria. To append a criteria to a query, you need to open the query in Design View. Next, you verify your preferred fields whereby you seek to clarify criteria. If any field is absent from the design grid, you import it by either dragging it from the query design window to the field grid, or by double-clicking the field (Double-clicking the field instantly adjoins it to the following blank column in the field grid.). Lastly, you enter the criteria in the Criteria row.

Criteria that you determine for various fields in the Criteria row are intergrated by using the AND operator. Put simply, the criteria defined in the City and BirthDate fields are understood in such a way:

City = “Chicago” AND BirthDate DateAdd (“ yyyy “, -40, Date())

Criteria for City and BirthDate

1. The City and BirthDate fields include criteria.

2. Only records where the value of the City field is Chicago will satisfy this criterion.

3. Only records of those who are at least 40 years old will satisfy this criterion.

4. Only records that meet both criteria will be included in the result.

What if you want only one of these conditions to be met? In other words, if you have alternate criteria, how do you enter them?

If you have alternate criteria, or two sets of independent criteria where it is sufficient to satisfy one set, you use both the Criteria and the or rows in the design grid.

Alternate criteria

1. The City criterion is elaborated in the Criteria row.

2. The BirthDate criterion is defined in the or row.

Criteria confirmed in the Criteria and or rows are merged using the OR operator, as displayed below:

City = “Chicago” OR BirthDate DateAdd (“ yyyy “, -40, Date())

If you must arrange further choices, utilise the rows under the or row.

It is important to remember the following conditions prior to you proceeding with the examples:

  • If the criteria is temporary or dynamic, you can filter the query result as opposed to regularly amending the query criteria. A filter is a temporary criterion that alters the query result without changing the design of the query. For more information about filters, see the article Apply a filter to view select records in an Access database.
  • If the criteria fields are static, but your sought values do transform gradually, you can set a parameter query. A parameter query urges the user for field values, and then applies those values to produce the query criteria. For more information about parameter queries, see the article Use parameters in queries and reports.

Criteria for Text, Memo, and Hyperlink fields

Note: Beginning in Access 2013, Text fields are now named Short Text and Memo fields are now named Long Text.

The displayed examples are for the CountryRegion field in a query that is linked to a table that holds contacts information. The criterion is formulated in the Criteria row of the field in the design grid.

CountryRegion criteria

A criterion that you determine for a Hyperlink field is transferred to the display text portion of the field value as a presetting. To determine criteria for the destination Uniform Resource Locator (URL) portion of the value, apply the HyperlinkPart expression. The syntax for this expression is as shown: HyperlinkPart([Table1].[Field1],1) = “http://www.microsoft.com/&#8221;, where Table1 is the table’s name including the hyperlink field, Field1 is the hyperlink field, and http://www.microsoft.com is the URL you aim to copy.

To include records that…Use this criterionQuery result
Exactly match a value, such as China“China”Returns records where the CountryRegion field is set to China.
Do not match a value, such as MexicoNot “Mexico”Returns records where the CountryRegion field is set to a country/region other than Mexico.
Begin with the specified string, such as ULike U*Returns records for all countries/regions whose names start with “U”, such as UK, USA, and so on.

Note: When used in an expression, the asterisk (*) represents any string of characters — it is 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 UNot Like U*Returns records for all countries/regions whose names start with a character other than “U”.
Contain the specified string, such as KoreaLike “*Korea*”Returns records for all countries/regions that contain the string “Korea”.
Do not contain the specified string, such as KoreaNot Like “*Korea*”Returns records for all countries/regions that do not contain the string “Korea”.
End with the specified string, such as “ina”Like “*ina”Returns records 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”Returns records for all countries/regions that do not end in “ina”, such as China and Argentina.
Contain null (or missing) valuesIs NullReturns records where there is no value in the field.
Do not contain null valuesIs Not NullReturns records where the value is not missing in the field.
Contain zero-length strings“” (a pair of quotes)Returns records where the field is set to a blank (but not null) value. For example, records of sales made to another department might contain a blank value in the CountryRegion field.
Do not contain zero-length stringsNot “”Returns records where the CountryRegion field has a nonblank value.
Contains null values or zero-length strings“” Or Is NullReturns records where there is either no value in the field, or the field is set to a blank value.
Is not empty or blankIs Not Null And Not “”Returns records where the CountryRegion field has a nonblank, non-null value.
Follow a value, such as Mexico, when sorted in alphabetical order>= “Mexico”Returns records of all countries/regions, beginning with Mexico and continuing through the end of the alphabet.
Fall within a specific range, such as A through DLike “[A-D]*”Returns records 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”Returns records for USA and UK.
Contain one of the values in a list of valuesIn(“France”, “China”, “Germany”, “Japan”)Returns records for all countries/regions specified in the list.
Contain certain characters at a specific position in the field valueRight([CountryRegion], 1) = “y”Returns records for all countries/regions where the last letter is “y”.
Satisfy length requirementsLen([CountryRegion]) > 10Returns records for countries/regions whose name is more than 10 characters long.
Match a specific patternLike “Chi??”Returns records for countries/regions, such as China and Chile, whose names are five characters long and the first three characters are “Chi”.

Note: The characters ? and _, when used in an expression, represent a single character — these are also called wildcard characters. The character _ cannot be used in the same expression with the ? character, nor can it be used in an expression with the * wildcard character. You may use the wildcard character _ in an expression that also contains the % wildcard character.

Criteria for Number, Currency, and AutoNumber fields

The below illustrations apply to the UnitPrice field in a query that is relevant to a table that holds products’ information. The criterion is made clear in the Criteria row of the field in the query design grid.

UnitPrice criteria
To include records that…Use this criterionQuery Result
Exactly match a value, such as 100100Returns records where the unit price of the product is $100.
Do not match a value, such as 1000Not 1000Returns records where the unit price of the product is not $1000.
Contain a value smaller than a value, such as 100< 100
<= 100
Returns records where the unit price is less than $100 (<100). The second expression (<=100) displays records where the unit price is less than or equal to $100.
Contain a value larger than a value, such as 99.99>99.99
>=99.99
Returns records where the unit price is greater than $99.99 (>99.99). The second expression displays records where the unit price is greater than or equal to $99.99.
Contain one of the two values, such as 20 or 2520 or 25Returns records where the unit price is either $20 or $25.
Contain a value that falls with a range of values>49.99 and <99.99
-or-
Between 50 and 100
Returns records where the unit price is between (but not including) $49.99 and $99.99.
Contain a value that falls outside a range<50 or >100Returns records where the unit price is not between $50 and $100.
Contain one of many specific valuesIn(20, 25, 30)Returns records where the unit price is either $20, $25, or $30.
Contain a value that ends with the specified digitsLike “*4.99”Returns records where the unit price ends with “4.99”, such as $4.99, $14.99, $24.99, and so on.

Note: The characters * and %, when used in an expression, represent any number of characters — these are also called wildcard characters. The character % cannot be used in the same expression with the * character, nor can it be used in an expression with the ? wildcard character. You may use the wildcard character % in an expression that also contains the _ wildcard character.
Contain null null (or missing) valuesIs NullReturns records where no value is entered in the UnitPrice field.
Contain non-null valuesIs Not NullReturns records where the value is not missing in the UnitPrice field.

Criteria for Date/Time fields

The embellished examples relate to the OrderDate field in a query linked to a table that retains Orders information. The criterion is made apparent in the Criteria row of the field in the query design grid.

OrderDate criteria
To include records that …Use this criterionQuery result
Exactly match a value, such as 2/2/2006#2/2/2006#Returns records of transactions that took place on Feb 2, 2006. Remember to surround date values with the # character so that Access can distinguish between date values and text strings.
Do not match a value, such as 2/2/2006Not #2/2/2006#Returns records of transactions that took place on a day other than Feb 2, 2006.
Contain values that fall before a certain date, such as 2/2/2006< #2/2/2006#Returns records of transactions that took place before Feb 2, 2006.To view transactions that took place on or before this date, use the <= operator instead of the operator.
Contain values that fall after a certain date, such as 2/2/2006> #2/2/2006#Returns records of transactions that took place after Feb 2, 2006.To view transactions that took place on or after this date, use the >= operator instead of the > operator.
Contain values that fall within a date range>#2/2/2006# and <#2/4/2006#Returns records where the transactions took place between Feb 2, 2006 and Feb 4, 2006.You can also use the Between operator to filter for a range of values, including the end points. For example, Between #2/2/2006# and #2/4/2006# is the same as >=#2/2/2006# and <=#2/4/2006# .
Contain values that fall outside a range<#2/2/2006# or >#2/4/2006#Returns records where the transactions took place before Feb 2, 2006 or after Feb 4, 2006.
Contain one of two values, such as 2/2/2006 or 2/3/2006#2/2/2006# or #2/3/2006#Returns records of transactions that took place on either Feb 2, 2006 or Feb 3, 2006.
Contain one of many valuesIn (#2/1/2006#, #3/1/2006#, #4/1/2006#)Returns records where the transactions took place on Feb 1, 2006, March 1, 2006, or April 1, 2006.
Contain a date that falls in a specific month (irrespective of year), such as DecemberDatePart(“m”, [SalesDate]) = 12Returns records where the transactions took place in December of any year.
Contain a date that falls in a specific quarter (irrespective of year), such as the first quarterDatePart(“q”, [SalesDate]) = 1Returns records where the transactions took place in the first quarter of any year.
Contain today’s dateDate()Returns records of transactions that took place on the current day. If today’s date is 2/2/2006, you see records where the OrderDate field is set to Feb 2, 2006.
Contain yesterday’s dateDate()-1Returns records of transactions that took place the day before the current day. If today’s date is 2/2/2006, you see records for Feb 1, 2006.
Contain tomorrow’s dateDate() + 1Returns records of transactions that took place the day after the current day. If today’s date is 2/2/2006, you see records for Feb 3, 2006.
Contain dates that fall during the current weekDatePart(“ww”, [SalesDate]) = DatePart(“ww”, Date()) and Year( [SalesDate]) = Year(Date())Returns records of transactions that took place during the current week. A week starts on Sunday and ends on Saturday.
Contain dates that fell during the previous weekYear([SalesDate])* 53 + DatePart(“ww”, [SalesDate]) = Year(Date())* 53 + DatePart(“ww”, Date()) – 1Returns records of transactions that took place during the last week. A week starts on Sunday and ends on Saturday.
Contain dates that fall during the following weekYear([SalesDate])* 53+DatePart(“ww”, [SalesDate]) = Year(Date())* 53+DatePart(“ww”, Date()) + 1Returns records of transactions that will take place next week. A week starts on Sunday and ends on Saturday.
Contain a date that fell during the last 7 daysBetween Date() and Date()-6Returns records of transactions that took place during the last 7 days. If today’s date is 2/2/2006, you see records for the period Jan 24, 2006 through Feb 2, 2006.
Contain a date that belongs to the current monthYear([SalesDate]) = Year(Now()) And Month([SalesDate]) = Month(Now())Returns records for the current month. If today’s date is 2/2/2006, you see records for Feb 2006.
Contain a date that belongs to the previous monthYear([SalesDate])* 12 + DatePart(“m”, [SalesDate]) = Year(Date())* 12 + DatePart(“m”, Date()) – 1Returns records for the previous month. If today’s date is 2/2/2006, you see records for Jan 2006.
Contain a date that belongs to the next monthYear([SalesDate])* 12 + DatePart(“m”, [SalesDate]) = Year(Date())* 12 + DatePart(“m”, Date()) + 1Returns records for the next month. If today’s date is 2/2/2006, you see records for Mar 2006.
Contain a date that fell during the last 30 or 31 daysBetween Date( ) And DateAdd(“M”, -1, Date( ))A month’s worth of sales records. If today’s date is 2/2/2006, you see records for the period Jan 2, 2006. to Feb 2, 2006
Contain a date that belongs to the current quarterYear([SalesDate]) = Year(Now()) And DatePart(“q”, Date()) = DatePart(“q”, Now())Returns records for the current quarter. If today’s date is 2/2/2006, you see records for the first quarter of 2006.
Contain a date that belongs to the previous quarterYear([SalesDate])*4+DatePart(“q”,[SalesDate]) = Year(Date())*4+DatePart(“q”,Date())- 1Returns records for the previous quarter. If today’s date is 2/2/2006, you see records for the last quarter of 2005.
Contain a date that belongs to the next quarterYear([SalesDate])*4+DatePart(“q”,[SalesDate]) = Year(Date())*4+DatePart(“q”,Date())+1Returns records for the next quarter. If today’s date is 2/2/2006, you see records for the second quarter of 2006.
Contain a date that falls during the current yearYear([SalesDate]) = Year(Date())Returns records for the current year. If today’s date is 2/2/2006, you see records for the year 2006.
Contain a date that belongs to the previous yearYear([SalesDate]) = Year(Date()) – 1Returns records of transactions that took place during the previous year. If today’s date is 2/2/2006, you see records for the year 2005.
Contain a date that belongs to next yearYear([SalesDate]) = Year(Date()) + 1Returns records of transactions with next year’s date. If today’s date is 2/2/2006, you see records for the year 2007.
Contain a date that falls between Jan 1 and today (year to date records)Year([SalesDate]) = Year(Date()) and Month([SalesDate]) <= Month(Date()) and Day([SalesDate]) <= Day (Date())Returns records of transactions with dates that fall between Jan 1 of the current year and today. If today’s date is 2/2/2006, you see records for the period Jan 1, 2006 to to 2/2/2006.
Contain a date that occurred in the past< Date()Returns records of transactions that took place before today.
Contain a date that occurrs in the future> Date()Returns records of transactions that will take place after today.
Filter for null (or missing) valuesIs NullReturns records where the date of transaction is missing.
Filter for non-null valuesIs Not NullReturns records where the date of transaction is known.

Criteria for Yes/No fields

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 valueResult
Yes, True, 1, or -1Tested 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 0Tested 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 0No results if it’s the only criteria value in the field.
Any character string other than Yes, No, True, or FalseQuery fails to run due to Data type mismatch error.

Criteria for other fields

Attachments –   In the Criteria row, type Is Null to encompass records that do not include any attachments. Write Is Not Null to embody records that have attachments.

Lookup fields  – There are two variants of Lookup fields: those that lookup values in an current data source (by using a foreign key), and those that are associated with a list of values originated at the point in which the Lookup field is developed.

Lookup fields that are related to a list of precise values belong to the Text data type, and accurate criteria are equal in comparison to the other text fields.

The criteria you can use in a Lookup field based on values from an existing data source vary on the data type of the foreign key, rather than the data type of the data being looked up. For example, you may have a Lookup field that displays Employee Name, but uses a foreign key that is of the Number data type. Because the field stores a number instead of text, you use criteria that work for numbers; that is, >2.

If you do not know the data type of the foreign key, you can inspect the source table in Design view to determine the data types of the field. To do this:

  • Find the source table in the Navigation Pane.
  • Access the table in Design view by either:
  • Selecting the table, and then pressing CTRL+ENTER
  • Right-clicking the table, and then choosing Design View.
  • The data type for each field is illustrated in the Data Type column of the table design grid.

Multivalued fields  –  Data in a multivalued field are stored as rows in a hidden table that Access designs and occupies to symbolise the field. In Query Design View, this is indicated in the Field List by using an expandable field. To apply criteria for a multivalued field, you offer criteria for one row of the concealed table. To do this:

  • Formulate a query containing the multivalued field, and open it in Design view.
  • Expand the multivalued field by pressing the plus symbol (+) beside it — if the field is already expanded, this is a minus symbol (). Just ubderbelow the name of the field, you will see a field representing a single value of the multivalued field. This field will have the same name as the multivalued field, with the string .Value appended.
  • Drag the multivalued field and its single value field to individual columns in the design grid. If you want to see only the complete multivalue field in your results, unmark the Show checkbox for the single value field.
  • Enter your criteria in the Criteria row for the single value field, using criteria that is relevant for whatever type of data the values represent.
  • Each value in the multivalued field will be independently assessed using the criteria you supply. For example, you may have a multivalued field that stores a list of numbers. If you supply the criteria >5 AND <3, any record where there is at least one value greater than 5 and one value less than 3 will match.

See Also

Introduction to queries

Create a simple select query

Advertisements

Leave a Reply

%d bloggers like this: