Searching for Data

See Also  

Once you display the data you want to see, you can print the information in a report. See Chapter 18, Preparing and Printing Reports in Microsoft Office Access 2003, to find out how to learn to print your displayed data.

Depending on what you want to do with the information you find, you can use a couple of techniques to search for data. The first technique helps you find a record you want. You might choose to search for a customer s address, for example, or to update a product code in your Products table. The second method involves searching for groups of records, by applying a filter that eliminates those records that are irrelevant. This section introduces you to both procedures.

Finding Specific Data

When you want to move to a specific record in your database, you first need to know what you re looking for. Are you looking for the record of a customer with the last name of Davis? That s enough to do a search. Here are the steps:

1. Display the data table or form you want to search.

2. Click in the field you want to search.

3. Choose Find from the Edit menu or press Ctrl+F. The Find And Replace dialog box appears (as shown in Figure 17-1). Notice that the selected field appears in the Look In box.

   
   Figure 17-1: You can search for specific data items using the Find And Replace dialog box.

   Aha! ”Search Everywhere

   If you re working with a large table and you re uncertain where the data you need to change is stored, you can have Access search the entire table by clicking the Look In down arrow, and choosing the name of the table.

    

4. In the Find What box, type the word, phrase, or characters you want to find.

5. Click the Match down arrow and choose whether you want Access to look for the information you typed in any part of the field, as the whole field (meaning that Access will display only records that show exactly what you typed in the field you selected), or as the beginning of the field entry.

6. In the Search list, choose whether you want Access to search Down through the database from the current selection, Up to the beginning of the database, or All the way through the entire database.

7. If you want Access to search for the capitalization you entered in the Find What box, click the Match Case check box.

   Aha! ”If Appearances Are Deceiving ...

   The Search Fields As Formatted check box enables you to search for data as it is displayed, instead of how it is stored. This means, for example, that although a value might be stored as 2345, you can search for $2,345, if that s the way the information appears. This search feature can add extra time while you re waiting for results, however, so if you want your data fast, leave this box unchecked.

    

8. Click Find Next. If you are using a form, Access displays the next form with the information you re looking for. If you are using datasheet view, Access highlights the field entry that matches your criteria.

And Don t Forget Replace

The Replace feature comes in handy for times when you need to make a change throughout your database. For some reason, here in Indiana, they keep changing our area codes. Phone numbers in some areas that used to have a 317 prefix now have a 765 prefix. Talk about messing up someone s database! Now all your entries for Madison County are wrong. How will you fix it? Simply perform the following steps:

1. Open the data table with the entries you want to change.

2. Click in the field you want to change (in this case, Phone).

3. Press Ctrl+H (or choose Replace from the Edit menu).

4. In the Find What box, type the data you want to find (in this case, 317).

5. In the Replace With box, type the information you want to insert (in this case, 765).

6. Set the Match and Search options, then click Replace to replace the next occurrence of the data, or click Replace All to search the entire database and change all occurrences within the data table.

 

Applying Filters

A filter is a bit like a water purifier ”it strains out all the elements you don t want. When you apply a filter to your data table, you tell Access to display only the data records that match what you re looking for. Here s the process:

1. Display the table or form to which you want to apply the filter.

2. Choose Filter from the Records menu, then choose one of the following:

   • Filter By Form displays a form in which you can enter your filter conditions.

   • Filter By Selection allows you to choose the filter by clicking existing data in the record.

   • Filter Excluding Selection eliminates the selected data, and displays everything else.

   • Advanced Filter/Sort displays a window in which you can construct a more detailed filter for your database.

3. For the simplest filtering tasks , you can select Filter By Form. A blank form appears (if you re viewing the datasheet, a blank table with one row appears).

4. Click in the field you want to use as a filter, then click the drop-down arrow that appears, and select your choice from the list. Figure 17-2 shows an Owner filter applied to the Contact Title field.

   
   Figure 17-2: When you choose Filter By Form, you use a blank form or datasheet row to choose the data by which you want to filter your records.

5. Select any additional fields you d like to use as part of the filter.

6. Click Apply Filter in the toolbar. Only the records that pass muster with your filter are displayed.

Aha! ”Getting Rid of Unwanted Filters

Use Remove Filter/Sort from the Records menu (or click the Remove Filter tool on the toolbar) when you want to return the table or form to its full record display. If you want to use the filter again, you can choose Apply Filter/Sort from the Records menu (or click Apply Filter in the toolbar) to reapply the most recently used filter.