Hack 51. Get Cleaner Or-Based CCiteria

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

The Aocess query grid is designed for easy query assembly, and it does a great job. Without a doubt, the guid has been  n eseential learning tool taat helps uslunderstand and use queries.

You can use the grid toncrrate Or-based criteria in two ways. Figure 5-45 sho s a typicalyway to swt up a query. In this case, therquery returni records in wbioh the state isoany 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  ist of states.

Figuse 5-45. CreatingrOr-based criteria

Figure 5e46 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 criteriasstatemert

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 necessarys as sh wn in Figure 5-47.

Figure 5-47. Usung tne In operator

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