Find and Replace

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.

Figure 7.1. The Find and Replace dialog box.

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."

Wildcards

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 hitsthat 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.

Table 7.1. Using Wildcards in Access Find

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.

A Find Example

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.

1.	Download the NiftyLionsChap7.mdb file to a convenient folder on your hard drive and open it.
2.	Click Forms and open the frmCustomers form, which is in Datasheet view. Maximize the window.
3.	Click in the first row of the First Name column. Choose Edit, Find (or press Ctrl+F).
4.	In Find What, type j*n. Access finds all values that begin with j, end in n, and might or might not have any additional number of characters in between.
5.	Your other settings should be as follows: Look In: First Name You took the trouble to move to the First Name field, so take advantage of that position by searching on the field rather than the entire datasheet. Match: Whole Field You don't want accidental hits such as Juanita or Jannie, which would be found for Any Part of Field. Search: All You want to search in all directions. Match Case: Deselect In a small datasheet, Access will find the text quickly, regardless of whether you capitalize the Find What term and select Match Case, or don't capitalize it and leave Match Case deselected. This option is useful when you want to find capitalized text when there are many similar values that are all lowercase. Search Fields As Formatted: Deselect Actually, in this case, it won't make a difference whether you select this option. But it's good habit to get into.
6.	Click Find Next. Access finds Juan.
7.	Click Find Next again. Access finds Justin. Click Find Next again, and Access finds Jason.
8.	Click Find Next again, and Access tells you that the search item can't be found. In other words, there are no more matches.
9.	Click OK.

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.

1.	Edit Find What to j????n. Access will find first names that begin with j, end in n, and have four characters in between.
2.	Click Find Next. Access finds Justin. Click Find Next again; there are no more matches to be found. Click OK. Note that, unlike the previous search using the * wildcard, Access didn't find Juan or Jason. In both of these names, there are only three letters between the j and n, not four, as required by the four ? wildcards.

A Find and Replace Example

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.

1.	Without closing the Find dialog box, click in the Street Address field.
2.	Click the Replace tab.
3.	Highlight the existing text in Find What and type carranzo.
4.	Type Karanzo in Replace With.
5.	Your settings should be as follows: Look In: Street Address As before, you moved here purposely for the added speed afforded by searching one field instead of an entire table, so take advantage of it. Match: Any Part of Field You're not sure where Carranzo is in the value, and you're pretty sure it's not the whole value, so have Access look for it in any part. Search: All You want to search in all directions. Match Case: Deselect You can avoid accidental hits by capitalizing the search term and selecting this option. But ignoring capitalization is probably simpler, especially in small tables, and especially for incompetent typists. Search Fields As Formatted: Deselect As noted previously, it's usually best to keep this option deselected.
6.	Click Find Next. Verify the address is for Manuel Simons. (If necessary, move the Find and Replace dialog box.) Click Replace to edit the street address.
7.	Click Find Next, just to make sure there were no other Manuel Simons on Carranzo. Click OK in the "Search Item Not Found" message.
8.	Close Find and Replace. Close the form.

Default Search/Edit Options

To a limited extent, you can change the default selections in the Find dialog boxthat 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.