Adding Criteria to Queries

Often what you want to do with a query is not just display data from different tables in one place, but somehow filter that data so that you only see data that meets certain criteria. Queries are the tool in Access designed to do that for you and you can use these queries later to create a form or report that shows only a subset of the data in your database. The Criteria row in the query grid is where you define criteria. A criterion must be defined so that it applies to one field; you then put that criterion in the same column of the grid as the field it applies to. You can also have multiple criteria; that is, criteria for more than one field, or more than one criteria for one field .

• Query by Example
  You began to learn about creating criterion when you learned about filtering tables. In query Design view you can also query by example; that is, you can simply type the value that you want Access to find. For instance, you could edit the Project Hours query to limit the records shown to those that include ABC Webworks:

In Microsoft Access, criteria are used to filter query results to display only the records that meet specific conditions. Here's a step-by-step guide to using criteria to limit the results of a query:

1. Open the Query in Design View
   • Open your Microsoft Access database.
   • Go to the Queries section in the Navigation Pane.
   • Right-click the query you want to modify, and select Design View.
2. Add Fields to the Query
   • Ensure the fields you want to filter are in the query design grid.
   • If not, drag the required fields from the table to the query design grid.
3. Set the Criteria
   • In the Criteria row of the query design grid, enter the condition you want to apply for a specific field.
   • Examples of common criteria:
     • Exact match: Enter a specific value, e.g., ="New York" to filter for records where the field equals "New York."
     • Partial match: Use wildcards like Like "J*" to find values starting with "J."
     • Range: Use operators like >, <, >=, <=, BETWEEN, e.g., BETWEEN 10 AND 20.
     • Null values: Use Is Null to find empty fields or Is Not Null for non-empty fields.
     • Multiple criteria: Use AND or OR for compound conditions, e.g., > 10 AND < 50.
4. Test the Query
   • Switch to Datasheet View by clicking the View button in the toolbar to see the filtered results.
   • Verify the records meet the criteria.
5. Save the Query
   • Once satisfied, click Save to store the changes.

Example Scenario
Filter Employees Earning More Than $50,000:

• Field: Salary
• Criteria: >50000
• This displays all employees whose salary exceeds $50,000.

Tips:

• Use Parameters: If you want to prompt the user for a value, use [Enter minimum salary:] in the criteria. When the query runs, it will ask the user to input a value.
• Combine Fields: You can apply criteria to multiple fields for more complex filters.
• Expression Builder: Use the Expression Builder to create advanced criteria.

By applying criteria effectively, you can tailor query results to your specific needs in Microsoft Access.

Often, though, your criteria will be more complicated than just finding a certain value in a certain field. When this is the case you need to use an operator.
View the Table below to see a table of the most commonly used relational operators.

Commonly Used Relational Operators
Operator	What it means
=	equal to (this operator is usually understood and you do not need to type it)
<>	Not equal to
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to
BETWEEN	Between two values i.e. BETWEEN 1 AND 5
NOT	Not equal to

Learn about using values in character expressions in the next lesson.