Specify Multiple Search Conditions for One Column

Background information

In some instances, you might want to apply a number of search conditions to the same data column. For example, you might want to:

• Search for several different names in an employee table or for employees who are in different salary ranges. This type of search requires an OR condition.
• Search for a book title that both starts with the word "The" and contains the word "Cook." This type of search requires an AND condition.

  Note   The information in this topic applies to search conditions in both the WHERE and HAVING clauses of a query. The examples focus on creating WHERE clauses, but the principles apply to both types of search conditions.

To search for alternative values in the same data column, you specify an OR condition. To search for values that meet several conditions, you specify an AND condition.

Specify an OR Condition

Using an OR condition enables you to specify several alternative values to search for in a column. This option expands the scope of the search and can return more rows than searching for a single value.

Tip

You can often use the IN operator instead to search for multiple values in the same data column.

1. In the Database window, click Queries   under Objects, click the query you want to open, and then click Design on the Database window toolbar.
2. In the Grid pane, add the column to search.
3. In the Criteria column for the data column you just added, specify the first condition.
4. In the Or à column for the same data column, specify the second condition.

   

The Query Designer creates a WHERE clause that contains an OR condition such as the following:

SELECT fname, lname
FROM employees
WHERE (salary < 30000) OR (salary > 100000)

Specify an AND Condition

Using an AND condition enables you to specify that values in a column must meet two (or more) conditions for the row to be included in the result set. This option narrows the scope of the search and usually returns fewer rows than searching for a single value.

Tip

If you are searching for a range of values, you can use the BETWEEN operator instead of linking two conditions with AND.

1. In the Database window, click Queries   under Objects, click the query you want to open, and then click Design on the Database window toolbar.
2. In the Grid pane, add the column to search.
3. In the Criteria column for the data column you just added, specify the first condition.
4. Add the same data column to the Grid pane again, placing it in an empty row of the grid.
5. In the Criteria column for the second instance of the data column, specify the second condition.

   

The Query Designer creates a WHERE clause that contains an AND condition such as the following:

SELECT title_id, title
FROM titles
WHERE (title LIKE '%Cook%') AND 
 (title LIKE '%Recipe%')