The best method for locating and editing a few values is often the Find and Replace dialog box, available from the Edit menu in a datasheet or form (see Figure 7.1). On the Find tab, you enter criteria in the Find What box, adjust the other settings in the dialog box, and click Find Next. Access continues to find values that meet the criteria you entered, until you get a message that it couldn't find any matches. The Replace tab works similarly, but it also offers the capability to edit data.
You've probably been using Find dialog boxes in Microsoft Office for years, so I won't belabor its basic functions. Some features are peculiar to Access, however, and are not entirely intuitive. Here are a few tips:
Position the cursor correctly— If you want to find values in a particular field instead of the entire table, make sure the cursor is in that field. Then select the field in Look In (instead of the alternative choice, which will be the entire table, form, or query).
Take care when choosing directional searches— Let's say you're working in the middle of a query. You're trying to find a few values you think are at the bottom of the datasheet, so you open the Search list and choose Down. Access searches to the end of the datasheet; if it doesn't find what it's looking for, it calls it quits. Access won't loop back and search the records at the top of the datasheet, nor will it ask you if you'd like that done. The Up selection works the same way, in the reverse direction. Thus, the default selection of All, which searches in all directions, is safest.
Avoid the Search Fields as Formatted option— Suppose the field you're searching uses the Long Date format; the value you want is displayed as Monday, November 15, 2004. If you search for 11/15/2004 with this option selected, Access won't find this correct date because the Find format differs from the format in the field. Unless you're specifically searching for values by including the formatting, leave Search Fields as Formatted deselected.
TIP If you're continually finding and replacing data in the same field, consider creating an index on that field to speed up the search. Indexes are discussed in Chapter 5, “Building Tables.” |
Poker players know well how powerful a wildcard can be. Just like any deuce or one-eyed jack, a wildcard in Access can match things unlike itself. In poker, however, a wildcard can usually match any one of 51 cards. That universality is not true of Access wildcards, each of which is limited to a specific set of values. The correct wildcard positioned strategically can greatly limit the number of accidental hits—that is, records that match the criteria you've chosen but are nevertheless unwanted. Table 7.1 summarizes the wildcards available and how they work. Wildcards are not limited to the Find command; they are extremely useful in filters and queries as well.
Name | Symbol | Function | Examples |
---|---|---|---|
Asterisk | * | Matches any number of characters | land* finds land, landing, land-grant; *land finds gland, hinterland, never-never land; *lan* finds land, plant, clandestine; l*n finds loan, Lyn, Lyndon |
Question mark | ? | Matches any single alphanumeric character | c??? finds call and cell and c356, but not car (must have four characters) |
Square brackets | [ ] | Match any one character between brackets | l[io]t finds lit and lot but not let |
Exclamation point | ! | Does not match the bracketed character | l[!i]t finds let and lot but not lit |
Hyphen | - | Matches any one character within the prescribed range | l[e-i]t finds let and lit but not lot; l[!e-i]t finds lot, but not let or lit |
Pound sign | # | Matches a numeric character in that position | m### finds m245 but not mt22 or mtc2 |
To find a symbol that can be used as a wildcard, enclose it in square brackets; for example, [#]83 finds #83. |
You likely don't need much help in using Find. But because it is such a useful tool, I've provided a few examples. Assume you're trying to find a customer whose first name you've forgotten, but you know that it begins with j and ends in n.
Suppose that, instead of an indeterminate number of letters, you're sure that the first name starts with j and ends in n and has six letters.
Now let's find and replace some text. Suppose you need to edit the record of Manuel Simons. You want to change the street name in the street address for this customer from Carranzo to Karanzo. Because Carranzo is probably fairly distinctive, it's better to search directly for that text string and then verify the customer name instead of search for a first or last name that's perhaps not so uncommon.
To a limited extent, you can change the default selections in the Find dialog box—that is, those that apply when you open it. On the Tools, Options, Edit/Find tab, you have three choices:
Fast Search— This is the default of the defaults. The whole field must be a match, and only the current field is searched.
General Search— This setting provides the widest search. It sets Match to Any Part of Field, and the entire table is searched.
Start of Field Search— The Match setting is set to Start of Field; Look In is set to the current field.
To change this option, click the type of search you want and choose OK. When you close the database, exit Access, and then reopen the database, your new selection will be in effect.
52.14.82.217