Chapter7.Find and Filter

Chapter 7. Find and Filter

This is the first of three chapters that will help you find, display, and retrieve specific records. It focuses on the Find dialog box and, more important, filters. The chapter also teaches you about wildcards and expressions, which are essential for using Find, filtering records and, more important, creating queries. Although I don't discuss queries until Chapter 8, "Queries," many query basicsAND and OR criteria, the design grid, and operatorsare introduced here.

In a well-worn conceit of old TV sitcoms, Ward Cleaver (or Jim Anderson, or Ozzie Nelson) is pillaging the hallway closet. He's looking for his fishing tackle, bowling ball, or soldering iron. Finally he calls out to June (or Margaret, or Harriet) for help, complaining that he can never find anything in this darn house. Mom comes running, takes one look at the shelves, and, with a look that combines tender love and seething exasperation, hands Dad one of his cherished possessions.

If your database is organized with anything like the sparkling economy and ruthless efficiency of June Cleaver's closets, you should have little trouble finding the information you need in Access. Unlike Edison inventing the light bulb, locating the right records in Access should be 1% perspiration and 99% inspiration. Access provides powerful tools for tracking down anything from a few letters in a single value to thousands of records that meet numerous conditions.

There's a progression in the power and scope of Access's "get-it-for-me" tools. At the bottom is the simple Find command, useful for locating and editing a few values. You've used Find often in other Office programs, such as Word. But as a program dedicated to storing and retrieving data, Access has some special features and functions in its Find tool.

The next most robust is the filter, which separates specific records within a datasheet or form. You can quickly segregate records based on certain conditions, or criteria, you set. Users can employ filters for a quick view of customers from a certain state, books of a certain author, or detergents of a certain manufacturer. You can easily remove, reapply, or change the filter to fit your needs.

The creativity and effort you have invested in producing a truly relational database comes to fruition in queries, which, unlike filters, are separate objects in the Database window. Besides retrieving specific records from one table (and often more tables), you can use queries to do calculations, create forms and reports based on the query, make changes to tables, and perform other key functions. Tables might be the foundation of your database, but most of your actual work will be accomplished through queries.

But I'm getting a little ahead of myself. In this chapter, the focus is on Find and filters; let's get to it.