Finding Matching Records in a Table

To search for and select records with field values that match (or partially match) a particular value, use Access's Find feature. To find Luleå (a relatively large city in northern Sweden close to the Arctic Circle) in the City field, follow these steps:

1. In the Customers table, select the field (City) you want to search by clicking its header button or by placing the cursor in that field.

2. Click the toolbar's Find button or choose Edit, Find to display the Find and Replace dialog (see Figure 7.4). You can also display this dialog by pressing Ctrl+F. The dialog opens with the Find page active by default.

   Figure 7.4. The Find and Replace dialog opens with the name of the selected field in the Look In list of the Find page. The Find Next button is disabled until you type an entry in the Find What text box.

   

3. Type the name of the city (Lulea) in the Find What text box (see Figure 7.5). The Find Next command button is enabled. The default values of the Match and Search lists are satisfactory at this point. Matching case or format is not important here, so clear the Search Fields As Formatted check box.

   Figure 7.5. A Whole Field match is selected by default, so type the entire value (Lulea for this example) to find in the Find What text box.

   

4. Click the Find Next button. If you don't have a Scandinavian keyboard, Access displays the message box shown in Figure 7.6. Click OK to dismiss the message box.

   Figure 7.6. If the Find feature doesn't find a match for your entry, you receive a "not found" message.

   

Note

If you select the field by clicking the field header button rather than by selecting all characters of a value in the field, the Find What value defaults to the datasheet's first cell value in the field or the last find criterion you chose.

The "not found" message indicates that the Find feature didn't locate a match in the City field of the entire table. Access missed your entry because the Scandinavian diacritical ° is missing over the "a" in Lulea. In the ANSI character set, "a" has a value of 97, and "å" has a value of 229.

Tip

To enter international (extended) characters in the Find What text box, type the English letters and then use the Windows XP or Windows 2000 Character Map (Charmap.exe) applet to find and copy the extended character to the Clipboard. (Don't worry about choosing the correct font.) Paste the character into the Find What text box at the appropriate location.

If the letters preceding an extended character are sufficient to define your search parameter, follow these steps to find Luleå:

1. Type Lule, omitting the a, in the Find What text box.

2. Select Start of Field from the Match drop-down list.

3. Click the Find Next button. Access finds and highlights Luleå in the City field (see Figure 7.7).

   Figure 7.7. Omitting the special Scandinavian character from the search and using the Start of Field search option finds Luleå.

   

You also can find entries in any part of the field. If you type ule in the Find What text box and choose Any Part of Field from the Match drop-down list, you get a match on Luleå. However, you could also match Thule, the location of the Bluie West One airfield (also known as Thule Air Force Base) in Greenland. (There's no actual entry for Thule in the Customers table.)

Tip

You can search all fields of the table for a match by opening the Look In list and selecting Tablename: Table. Searching all fields in a table for a matching entry is usually much slower than searching a single field, especially if you have an index on the field being searched. Unless you specify the Any Part of Field Match option, Access uses the index to speed the searching operation.

Following is a list of the options available in the Find dialog:

• To specify a case-sensitive search, mark the Match Case check box.

• To search by using the field's format, mark the Search Fields as Formatted check box. This way you can enter a search term that matches the formatted appearance of the field, such as [phone], rather than the native (unformatted) value (5105551212), if you applied a Format property value to the field. Using the Search Fields as Formatted option slows the search operation because indexes aren't used.

• To find additional matches, if any, click the Find Next button. If the Search option is set to Down, clicking the Find Next button starts the search at the current position of the record pointer and searches to the end of the table.

• To start the search at the last record of the table, select Up in the Search drop-down list.