MS Access

Finding and Replacing Information

The Find tab of the dialog box.
The Replace tab of the Find and Replace dialog box.

Finding specific records or information in a large database would be like finding a needle in a haystack if it weren't for the Find feature. Find allows you to quickly search tables, queries, and forms for specified texta critical database task. Select a field to search through all records in the current field only. This is usually quicker, especially if the field is indexed. Or select the datasheet or form to search through all fields in all records.

The is very useful. Imagine you are working on a huge database that tracks the feeding patterns of squirrels. You're almost finished when you realize that you've mistakenly referred to one of the species of squirrels you're trackingflying squirrelsnot by their proper scientific name "Sciuridae Glaucomys" but by the scientific name for the common gray squirrel "Sciuridae Sciurus." Yikes! It will take hours to go back and find every instance of "Sciuridae Sciurus" in your database and replace it with "Sciuridae Glaucomys." Or it could take you less than a minute if you use Access's Find and Replace function.

  1. Start Microsoft Access, open the Lesson 3 database, and double-click on the Employees table.

    First you need to put the cursor in the field that contains the data you want to look for. For this exercise we'll search the City field.

  2. Click anywhere in the City field.

    Here's how to open the Find and Replace dialog box.

  3. Click the Find button on the toolbar.

    Other ways to find information are to press Ctrl + F, or select Edit » Find from the menu.

    The Find and Replace dialog box appears with the Find tab in front, as shown in figure. You tell Access what you're looking for in the Find What box.

  4. In the Find What text box type Redmond.

    Also important are the following options:

    • Look In combo box: Allows you to search only the current field (which is faster) or all the fields in the entire table (which is slower).

    • Match combo box: See table 3-1 for a description of the Match combo box and its options.

    • Search combo box: Allows you to search up or down from the insertion point or search the whole document.

    • Match Case check box: Finds only text that has the same pattern of uppercase and lowercase characters as the text you specified.

    • Search Fields as Formatted check box: Check to search based on the format rather than the value.

  5. Click the Find Next button.

    Access jumps to the first (and only) occurrence of the word "Redmond" that it finds in the table.

  6. Click Cancel.

    The Find and Replace dialog box closes. You can also replace information in a database.

  7. Click anywhere in the Title field and select Edit » Replace from the menu.

    Another way to find and replace information is to press Ctrl + H.

    The Find and Replace dialog box appears with the Replace tab in front, as shown in figure.

  8. In the Find What text box type Sales Representative.

    You want to replace every occurrence of the phrase "Sales Representative" with the phrase "Sales Associate."

  9. Select the Replace With text box by clicking it or by pressing the Tab key and type Sales Associate.

  10. Click Replace All.

    Access finds all the occurrences of the phrase "Sales Representative" in the table and replaces them with the words "Sales Associate."

    Think before you use the Replace All buttonyou might not want it to replace every instance of a label or value! You can find and replace each individual occurrence of a label or value by clicking Find Next and Replace.

  11. Click Yes to acknowledge the warning and then click Cancel.

    The Find and Replace dialog box disappears and you're back to your datasheet. Notice how all the occurrences of the phrase "Sales Representative" have been replaced by the phrase "Sales Associate."

Using the Match List Options
Match Description

Whole Field

Finds only data that is exactly the same.

Example: John finds John, but not Johnson, or Sue and John.

Any Part of Field

Finds data anywhere in the field.

Example: John finds John, Johnson, and Sue and John.

Start of Field

Finds data only at the beginning of the field.

Example: John finds John and Johnson, but not Sue and John.


TO FIND INFORMATION:

  1. CLICK THE FIND BUTTON ON THE TOOLBAR.

    OR...

    PRESS CTRL + F.

    OR...

    SELECT

  2. ENTER THE TEXT YOU WANT TO SEARCH FOR IN THE FIND WHAT TEXT BOX.

  3. CLICK THE FIND NEXT BUTTON.

  4. REPEAT STEP 3 UNTIL YOU FIND THE TEXT YOU'RE LOOKING FOR.

TO FIND AND REPLACE INFORMATION:

  1. SELECT EDIT » REPLACE FROM THE MENU.

    OR...

    SELECT EDIT » FIND FROM THE MENU.

  2. ENTER THE TEXT YOU WANT TO SEARCH FOR IN THE FIND WHAT TEXT BOXENTER THE TEXT YOU WANT TO REPLACE THE WORD WITH IN THE REPLACE WITH TEXT BOX.

  3. CLICK THE FIND NEXT BUTTON.

  4. CLICK THE REPLACE BUTTON TO REPLACE THE TEXT.

  5. REPEAT STEPS 4 AND 5 IF THERE IS MORE THAN ONE OCCURRENCE THAT YOU WANT TO REPLACE.

    OR...

    PRESS CTRL + H

by BrainBellupdated
Advertisement: