Previous Page
Next Page

Hack 51. Get Cleaner Or-Based Criteria

Avoid using multiple rows in the query grid by using the In operator.

The Access query grid is designed for easy query assembly, and it does a great job. Without a doubt, the grid has been an essential learning tool that helps us understand and use queries.

You can use the grid to create Or-based criteria in two ways. Figure 5-45 shows a typical way to set up a query. In this case, the query returns records in which the state is any of six possible values. As you can see, however, if a few more states were to be included, it would become necessary to start scrolling vertically to work on the list of states.

Figure 5-45. Creating Or-based criteria


Figure 5-46 shows an alternate way to set up the Or criteria. The specified states are put on one row, with Or statements throughout. However, this design also suffers from becoming unwieldy if more states are added. Each additional state being added also requires another Or operator, so the expression can become quite long.

Figure 5-46. A long criteria statement


The In operator is the solution to this dilemma. The In operator is perfect for establishing Or-based criteria. Whereas in Figure 5-46 the inclusion of each state requires another Or operator, only one In operator is necessary, as shown in Figure 5-47.

Figure 5-47. Using the In operator


Using the In operator makes it easy to add more states to the criteria. Just make sure you separate each state abbreviation with a comma.

    Previous Page
    Next Page