Lesson 13: Finding Records

/en/accessxp/sorting-records/content/

Introduction

By the end of this lesson, you should be able to:

  • Perform a simple find
  • Use a wild card to find a record

Performing a simple find

When you have an extremely large database consisting of thousands of records, you may need to quickly locate one of them. Access provides an easy way to find individual records: the Find function.

To use the Find function:

  • Click anywhere in the field that contains the data you want to search.
  • Choose EditFind on the menu bar or click the Find button on the toolbar to open the Find and Replace dialog box.
  • In the Find What field, type the information you want to search. It may be a last name (e.g., Manning) or a portion of the last name (e.g., Man).

    Find and Replace Dialog Box
  • The Look In drop-down box defaults to the field or column where the cursor was positioned when the find was initiated. You can leave the definition as is if you know the information is contained in this field, or you can chose to search the entire table from the drop-down menu list.

    Look In Field
  • In the Match field, Access defaults to Whole Field but you can optionally select either Any Part of Field or Start of Field.

    Match Field
  • Click the Find Next button to search for the first occurrence of a record that matches the search.

    Found Record

Performing a simple find

  • Click the Cancel button if the record was found. The Find and Replace dialog box will close.

OR

  • Click the Find Next button to search for the next record that meets the criteria.

If Access did not find a match for the search, the Office Assistant will pop up to inform you that there were not any matched records found.

Item Not Found Dialog Box

Using wild cards to find records

If the simple Find did not find the record you sought, you can try typing an operator in the Find What text box of the Find and Replace dialog box. A common Access operator is a wild card, or a symbol representing one or more characters.

Wild Card

Usage

Example

*
(Asterisk)

Used at the beginning or the end of a search string to match one or more characters

Sch* finds Schlotz and Schwartz. *ing finds Wheeling and Browning.

?
(Question Mark)

Matches any single alphabetic character

Sp?ll finds spell and spill.

#
(Pound Sign)

Matches any single numeric character

9#1 finds 901, 911, 921, etc.

To search for a record using a wild card:

  • Choose EditFind on the menu bar, or click the Find button on the toolbar to open the Find and Replace dialog box.
  • In the Find What field, type the wild card combination (e.g., B*) to find the first record where the last name begins with the letter B.

    Find and Replace Dialog Box
  • Click the Find Next button to begin the search. The first record found that matches the search is highlighted.

    Found Record

    (The Office Assistant will provide notification if no matching records were found).
  • Click the Cancel button if the record was found. The Find and Replace dialog box will close.

OR

  • Click the Find Next button to search for the next record that meets the Find criteria.

Challenge!

  • Open the Contacts table in Datasheet view.
  • Perform a Find function against the Contacts:Table Look In field to locate the Margaret record.
  • Perform a Find Next function against the Contacts:Table Look In field to locate the William Henderson record.
  • Perform a Find function against the Contacts:Table Look In field to locate the Don Mattingly record. Reply to the system prompt.
  • Perform a Find function against the Contacts:Table Look In field and using a H* wild card combination to locate the first record. Perform and repeat a Find Next function against the same wild card combination as you move your way through the records in the database.
  • Close the Contacts table. If displayed, click No in response to the Do you want to save changes to the design of table 'Contacts'? system prompt.

/en/accessxp/filtering-records/content/