Examples of filter criteria

This topic lists examples of filter criteria that you can use to limit the number of records that are displayed in a form, query, report, or data access page that is part of an Access database.

If you are looking for help about how to apply various filters, see the topic Filter: Limit the number of records in a view or report.

Filter criteria is similar to a formula — it is a string that may consist of field references, operators (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.), and constants (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.). Filter criteria are also referred to as expressions in Access.

For example, here are some simple expressions that filter a view, such as a datasheet or form:

  • To show only records where the price is between 50 and 100, in the Price field, you enter:
    > 50 and < 100
  • To show only records where the last name equals "Smith," in the LastName field, you enter:
    Smith

If you are using the Filter By Selection (Filter By Selection: A technique for filtering records in a form or datasheet in which you retrieve only records that contain the selected value.) or Filter Excluding Selection (Filter Excluding Selection: A technique in which you filter records in a form or datasheet to retrieve only those records that don't contain the selected value.) technique, you do not need to write an expression. However, to filter by using Filter For (Filter For Input: A technique for filtering records that uses a value or expression that you enter to find only records that contain the value or satisfy the expression.), Filter By Form (Filter By Form: A technique for filtering data that uses a version of the current form or datasheet with empty fields in which you can type the values that you want the filtered records to contain.), or Advanced Filter/Sort window (Advanced Filter/Sort window: A window in which you can create a filter from scratch. You enter criteria expressions in the filter design grid to restrict the records in the open form or datasheet to a subset of records that meet the criteria.), you need to create an expression that specifies how to filter records. If you are using the Filter property of a report to filter records, you need to specify an expression.

Where to enter the filter criteria

The following illustration shows where you specify the expression. In a Filter By Form operation, you enter the expression in a data field (such as Category). In a Filter For operation, you enter the expression in a box next to the Filter For menu item. In an Advanced Filter/Sort operation, you enter the expression in a query cell (such as Criteria).

Different filter expressions are typed in different fields

The tables in the following sections contain lists of several filter expressions. These expressions use a variety of operators, functions, and characters with special meaning — also known as wildcard characters. Near the end of this section, links to topics explain how to write expressions and how to use operators, functions (function: A query that takes input parameters and returns a result like a stored procedure. Types: scalar (multistatement; returns one value), inline (one statement; an updateable table value), and table (multistatement; table value).), and wildcard characters.

ShowFilter text and memo fields

The following examples are for the Country field in the Sales query.

To... Use this criterion... To display
Filter for records with exact matches China Sales records for China.
Tip    To do this type of filtering faster, use Filter By Selection.
Filter for records with values other than the specified string Not Mexico Sales records for all countries/regions other than Mexico.
Tip    To do this type of filtering faster, use Filter Excluding Selection.
Filter for records where the field value begins with the specified string U*

Sales records for all countries/regions whose names start with "U", such as UK, USA, and so on.

 Note   When used in an expression, the character * represents any string of characters — it is also called a wildcard character. For a list of such characters, see the topic About using wildcard characters.

Filter for records where the field value does not start with the specified string Not U* Sales records for all countries/regions whose names start with a character other than "U".
Filter for records that contain the specified string Like "*Korea*" Sales records for all countries/regions that contain the string "Korea".
Filter for records that do not contain the specified string Not Like "*Korea*" Sales records for all countries/regions that do not contain the string "Korea".
Filter for records that end with the specified string Like "*ina" Sales records for all countries/regions whose names end in "ina", such as China and Argentina.
Filter for a range of values >= "Mexico" Sales records of all countries/regions, beginning with Mexico and continuing through the end of the alphabet.
Filter for a specific range Like "[A-D]*" Sales records for countries/regions whose names start with the letters "A" through "D".
Filter for multiple values "USA" Or "UK" Sales records for USA and UK.
Filter for a list of values In("France", "China", "Germany", "Japan") Sales records for all countries/regions specified in the list.
Filter based on the position of the specified string in the field value Right([Country], 1) = "y" Sales records for all countries/regions where the last letter is "y".
Filter based on the length of the field values Len([Country]) > 10 Sales records for countries/regions whose name is more than 10 characters long.
Filter for patterns Like "Chi??"

Sales 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.

Filter for null (or missing) values Is Null Sales records where there is no value in the field.
Filter for non-null values Is Not Null Sales records where the value is not missing in the field.
Filter for blanks "" (a pair of quotes) Sales 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 Country field.
Filter for nonblanks Not "" Sales records where the Country field has a nonblank value.

ShowFilter number, currency, and autonumber fields

The following examples are for the Price field in the Sales query.

To... Use this criterion... To display
Filter for records with exact matches 100 Sales records where the price of the product sold is $100.
Tip    To do this type of filtering faster, use Filter By Selection.
Filter for records with values other than the specified value Not 1000 Sales records where the price of the product sold is not $1000.
Tip    To do this type of filtering faster, use Filter Excluding Selection.
Filter for values smaller than the specified value < 100
<= 100
Sales records where the price is less than $100 (<100). The second expression (<=100) displays sales records where the price is less than or equal to $100.
Filter for values larger than the specified value >99.99
>=99.99
Sales records where the price is greater than $99.99 (>99.99). The second expression displays sales records where the price is greater than or equal to $99.99.
Filter for multiple values 20 or 25 Sales records where the price is either $20 or $25.
Filter for a range of values >49.99 and <99.99
-or-
Between 50 and 100
Sales records where the price is between (but not including) $49.99 and $99.99.
Filter for values outside a range <50 or >100 Sales records where the price is not between $50 and $100.
Filter for a list of values In(20, 25, 30) Sales records where the price is either $20, $25, or $30.
Filter for records that end with the specified numbers Like "*4.99"

Sales records where the 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.

Filter for null (or missing) values Is Null Sales records where no value is entered in the Price field.
Filter for non-null values Is Not Null Sales records where the value is not missing in the Price field.

ShowFilter date and time fields

The following examples are for the SalesDate field in the Sales query. Note that the date or time format that you use to specify the criteria do not need to match the format in which the value is stored in the database.

To... Use this criterion... To display
Filter for records with exact matches #2/2/2006# Sales 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.
Tip    To do this type of filtering faster, use Filter By Selection.
Filter for records with today's date Date() Sales records of transactions that took place on the current day. If today's date is 2/2/2006, you see records where the SalesDate field is set to Feb 2, 2006.
Filter for records with yesterday's date Date()-1 Sales 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.
Filter for the past 7 days' records Between Date() and Date()-6 Sales 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.
Filter for current month's records Year([SalesDate]) = Year(Now()) And Month([SalesDate]) = Month(Now()) Sales records for the current month. If today's date is 2/2/2006, you see records for Feb 2006.
Filter for previous month's records Year([SalesDate]) = Year(Now()) And Month([SalesDate]) = Month(Now()) - 1 Sales records for the previous month. If today's date is 2/2/2006, you see records for Jan 2006.
Filter for records for the preceding 30 or 31 days Between 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
Filter for current quarter's records Year([SalesDate]) = Year(Now()) And DatePart("q", [SalesDate]) = DatePart("q", Now()) Sales records for the current quarter. If today's date is 2/2/2006, you see records for the first quarter of 2006.
Filter for "year to date" records Year([SalesDate]) = Year(Now()) Sales records of transactions that have taken place since Jan 1 of the current year. If today's date is 2/2/2006, you see records for the period Jan 1, 2006 to to 2/2/2006.
Filter for last year's records Year([SalesDate]) = Year(Now()) - 1 Sales 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.
Filter for records with values other than the specified value Not #2/2/2006# Sales records of transactions that place on a day other than Feb 2, 2006.
Tip    To do this type of filtering faster, use Filter Excluding Selection.
Filter for values older than the specified value < #2/2/2006#

Sales 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.

Filter for values newer than the specified value > #2/2/2006#

Sales 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.

Filter for multiple values #2/2/2006# or #2/3/2006# Sales records of transactions that took place on either Feb 2, 2006 or Feb 3, 2006.
Filter for a range of values >#2/2/2006# and <#2/4/2006#

Sales 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# .

Filter for values outside a range <#2/2/2006# or >#2/4/2006# Sales records where the transactions took place before Feb 2, 2006 or after Feb 4, 2006.
Filter for a list of values In (#2/1/2006#, #3/1/2006#, #4/1/2006#) Sales records where the transactions took place on Feb 1, 2006, March 1, 2006, or April 1, 2006.
Filter for null (or missing) values Is Null Sales records where the date of transaction is missing.
Filter for non-null values Is Not Null Sales records where the date of transaction is known.

ShowFilter Yes/No and hyperlink fields

Yes/No    If you are using either Filter For or Advanced Filter/Sort, type Yes or No in the text entry box. If you are using Filter By Form, you see a check box in the field. Select or clear the check box to find matching records. If you are setting the Filter property of a report, compare the field to "Yes" or "No", such as Employee.Married = Yes.

Hyperlink    You can filter a hyperlink field (hyperlink field: A field that stores hyperlink addresses. In an Access database (.mdb), it's a field with a Hyperlink data type. In an Access project (.adp), it's a field that has the IsHyperlink property set to True.) based on the display text of the hyperlinks. You cannot filter based on the destination Uniform Resource Locator (URL) (Uniform Resource Locator (URL): An address that specifies a protocol (such as HTTP or FTP) and a location of an object, document, World Wide Web page, or other destination on the Internet or an intranet, for example: http://www.microsoft.com/.). Several of the common filters that apply to the text data type work on a hyperlink field. See the first table for sample expressions.

ShowFilter for top or bottom items

To filter for top or bottom items based on the values in a field, you need to create a top values query. For example, to see the five cities with the highest sales figures or the bottom 1 percent of salespersons with the lowest sales figures, you need to create a query and specify the criteria in the Top Values box in Query Design view. For step-by-step information, see the topic Show only the high or low values in a query.

ShowFilter for unique or duplicate values

To see if more than one record contains a specific value in a particular field, use Filter By Selection. However, to find duplicate records. and hide or delete them, see the topic Find, eliminate, or hide duplicate records in Access.

Learn how to write expressions

To learn more about writing expressions and to see the full list of operators, functions, and wildcard characters, see the following topics:

Create an expression

About using wildcard characters

Functions (by category)

Still looking for help?

Didn't find the expression you are looking for? Does an expression just not work as expected? Or do you need help creating a complex filter? You can get help from communities on the Web — by posting a question to Access experts and other users.

Learn about communities with Ask the Access community. If you want to learn more about posting questions to communities, see Start getting answers from other Office users.

Back to top  Back to top

 
 
Applies to:
Access 2003