Enter criteria to retrieve certain records (MDB)

 Note   The information in this topic applies only to a Microsoft Access database (.mdb).

  1. Open a query in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).

Alternatively, you can display the 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.) for a table, query, or form.

 Tip    Try Office 2010 In Access 2010, the Expression Builder has IntelliSense, so you can see what arguments your expression requires.
Watch a video or try Office 2010.


  1. In the Database window, click Open for the selected table, query, or form.
  2. On the Records menu, point to Filter and then click Advanced Filter/Sort.

  1. Click the first Criteria cell for the field you want to set criteria for.
  2. Enter the criteria expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) by typing it or by using the Expression Builder. To display the Expression Builder, right-click the Criteria cell, and then click Build.

To see examples of criteria that you can specify in a query, see the topic Examples of searching and filtering data.

Records retrieved meet the criteria in both columns

Callout 1 Retrieves orders shipped to Bon App on 8/11/2001.

If your query includes linked tables, depending on the type of data source to which you are linked, the values you specify in criteria on fields from the linked tables might be case-sensitive (case-sensitive: Capable of distinguishing between uppercase and lowercase letters. A case-sensitive search finds only text that is an exact match of uppercase and lowercase letters.) — that is, they must match the case of the values in the underlying table.

  1. To enter another expression in the same field or in another field, move to the appropriate Criteria cell and enter the expression. To filter for values that include characters, such as "#" or "?", enclose the special character in brackets ([]).

 Note   To prompt the user to specify criteria for a query before retrieving records, create a parameter query. A parameter query, when run, displays its own dialog box that prompts you for information, such as criteria for retrieving records or a value you want to insert in a field. You can design the query to prompt you for more than one piece of information; for example, you can design it to prompt you for two dates. Access can then retrieve all records that fall between those two dates.

Applies to:
Access 2003