Hack 51. Get Cleaner Or-Based Criteria
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.