Share via

How to: Specify Multiple Search Conditions for One Column

  • Article

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. For details about creating HAVING clauses, see How to: Specify Conditions for Groups.

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.

Note

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

Specifying 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. For details, see Comparison Operators (Visual Database Tools).

To specify an OR condition

  1. In the Criteria pane, add the column to search.

  2. In the Filter column for the data column you just added, specify the first condition.

  3. In the Or... column for the same data column, specify the second condition.

The Query and View 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)

Specifying 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. For details, see Comparison Operators (Visual Database Tools).

To specify an AND condition

  1. In the Criteria pane, add the column to search.

  2. In the Filter column for the data column you just added, specify the first condition.

  3. Add the same data column to the Criteria pane again, placing it in an empty row of the grid.

  4. In the Filter 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%')

See Also

Concepts

Conventions for Combining Search Conditions in the Criteria Pane

Reference

Comparison Operators (Visual Database Tools)

Other Resources

Specifying Search Criteria