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 in the City field Luleå (a relatively large city in northern Sweden close to the Arctic Circle), follow these steps:
1. | Select the field—City—you want to search by clicking its header button or placing the caret in the field. |
2. |
Figure 6.4. The opened Find in Field dialog with the City field selected.
|
3. | Type the name of the city, Lulea, in the Find What text box (see Figure 6.5). Making an entry in the Find What text box enables the Find First and Find Next command buttons. |
4. | Select Whole Field from the Match drop-down list. (The other choices, Start of Field and Any Part of Field, are just as effective in this case.) The default value of the Search option button is satisfactory, and matching case or format is not important here. |
5. | Click the Find First button. If you do not have a Scandinavian keyboard, Access displays the "finished searching" message box shown in Figure 6.6. |
Figure 6.5. Attempting to find Luleå in the City field.
Figure 6.6. The message box that appears when Access cannot find a match to the content of the Find What text box.
The "finished searching" message indicates that the Find feature did not locate a match between the present position of the record pointer and the last record of the 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.
To enter international (extended) characters in the Find What text box, type the English letters and then use Windows 95's or Windows NT 4.0's 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 this set of 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 First button. Access finds and highlights Luleå in the City field, as shown in Figure 6.7. |
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 is no actual entry for Thule in the Customers table.)
Figure 6.7. Finding a record that contains a special character.
Note
Searching all fields for an 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 option, Access uses the index to speed the searching operation.
Following is a list of the options available in the Find in Field 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 rather than the native (unformatted) value if you applied a Format property value to the field. Using the Search Fields as Formatted option slows the search operation; indexes are not used.
To find additional matches, if any, click the Find Next button. If the Search option is set to Down, clicking the Find First button starts the search at the first record in the table, regardless of the current position of the record pointer. If the Search option is set to All, clicking the Find First button starts the search at the current record pointer.
To start the search at the last record of the table, select Up in the-Search drop-down list.
SQL has no direct equivalent to a Find First or Find Next operation. SQL is a set-oriented language, so the SQL statement
SELECT * FROM Customers WHERE City = "Luleå"
returns the set of all records that meet the criterion. The SQL CURSOR construct enables you to move between records of a set, but the CURSOR reserved word is not supported in Access SQL and its use is beyond the scope of the SQL discussion in this chapter.