How to Find Data in Access 2016

By Laurie Ulrich Fuller, Ken Cook

When you want to track down a particular record right now, creating a query for the job is overkill. Fortunately, Access 2016 has a very simple way to find one specific piece of data in your project’s tables and forms: the Find command.

Find is found — big surprise here — in the Find section of the Home tab, accompanied by a binoculars icon. You can also get to Find by pressing Ctrl+F to open the Find dialog box.

Although the Find command is pretty easy to use, knowing a few tricks makes it even more powerful, and if you’re a Word or Excel user, you’ll find the tricks helpful in those applications, too — because the Find command is an Office-wide feature.

Finding anything fast

Using the Find command is a very straightforward task. Here’s how it works:

  1. Open the table or form you want to search.

    Note that the Find command works in Datasheet view and with Access forms and becomes available as soon as a table or form is opened.

  2. Click in the field that you want to search.

    The Find command searches the current field in all the records of the table, so be sure to click the correct field before starting the Find process. Access doesn’t care which record you click; as long as you’re on a record in the correct field, Access knows exactly which field you want to Find in.

  3. Start the Find command.

    You can either click the Find button in the Find section of the Home tab or press Ctrl+F.

    The Find and Replace dialog box opens, ready to serve you.

  4. Type the text you’re looking for in the Find What box.

    Take a moment to check your spelling before starting the search. Access is pretty smart, but it isn’t bright enough to figure out that you actually meant plumber when you typed plumer.

    The Find and Replace dialog box.

    The Find and Replace dialog box.
  5. Click Find Next to run your search.

    • If the data you seek is in the active field, the Find command immediately tracks down the record you want.

      The cell containing the data you seek is highlighted.

      What if the first record that Access finds isn’t the one you’re looking for? Suppose you want the second, third, or the fourteenth John Smith in the table? No problem; that’s why the Find and Replace dialog box has a Find Next button. Keep clicking Find Next until Access either works its way down to the record you want or tells you that it’s giving up the search.

    • If Find doesn’t locate anything, it laments its failure in a small dialog box, accompanied by this sad statement:

      Microsoft Access finished searching the records. The search item was not found.

If Find didn’t find what you were looking for, you have a couple of options:

  • You can give up by clicking OK in the small dialog box to make it go away.

  • You can check the search and try again (you’ll still have to click OK to get rid of the prompt dialog box). Here are things to check for after you’re back in the Find and Replace dialog box:

    • Make sure that you clicked in the correct field and spelled everything correctly in the Find What box.

      You can also check the special Find options to see whether one of them is messing up your search.

    • If you ended up changing the spelling or options, click Find Next again.

Shifting Find into high gear

Sometimes just typing the data you need in the Find What box doesn’t produce the results you need:

  • You find too many records (and end up clicking the Find Next button endlessly to get to the one record you want).

  • The records that match aren’t the ones you want.

The best way to reduce the number of wrong matches is to add more details to your search, which will reduce the number of matches and maybe give you just that one record you need to find.

Access offers several tools for fine-tuning a Find. To use them, open the Find and Replace dialog box by either

  • Clicking the Find button on the Home tab

    or

  • Pressing Ctrl+F

If your Find command isn’t working the way you think it should, check the following options. Odds are that at least one of these options is set to exclude what you’re looking for.

Look In

By default, Access looks for matches only in the current field — whichever field you clicked in before starting the Find command. To tell Access to search the entire table instead, choose Current Document from the Look In drop-down list.

To search the entire table, change Look In.

To search the entire table, change Look In.

Match

Your options are as follows:

  • Any Part of Field: Allows a match anywhere in a field (finding Richard, Ulrich, and Lifestyles of the Rich and Famous). This is the default.

  • Whole Field: This requires that the search terms (what you type in the Find What box) be the entirety of the field value. So Rich won’t find Ulrich, Richlieu, Richard, or Richmond. It finds only Rich.

  • Start of Field: Recognizes only those matches that start from the beginning of the field. So Rich finds Richmond, but not Ulrich.

    This option allows you to put in just part of a name, too, especially if you know only the beginning of a name or the start of an address.

To change the Match setting, click the down arrow next to the Match field and then make your choice from the drop-down menu that appears.

Using the Match option.

Using the Match option.

Search

If you’re finding too many matches, try limiting your search to one particular portion of the table with the help of the Search option. Search tells the Find command to look either

  • At all the records in the table (the default setting)

    or

  • Up or down from the current record

Clicking a record halfway through the table and then telling Access to search Down from there confines your search to the bottom part of the table.

Fine-tune your Search settings by clicking the down arrow next to the Search box and choosing the appropriate offering from the drop-down menu.

Match Case

Checking the Match Case check box makes sure that the term you search for is exactly the same as the value stored in the database, including the same uppercase and lowercase characters.

This works really well if you’re searching for a name, rather than just a word, so that rich custard topping is not found when you search for (capital R) Rich in the entire table.

Search Fields as Formatted

This option instructs Access to look at the formatted version of the field rather than the actual data you typed. By “formatted” this means with formatting applied to the content – numbers formatted as percentages, dates set as short dates vs medium dates, and so on.

Limiting the search in this way is handy when you’re searching for dates, stock-keeping unit IDs, or any other field with quite a bit of specialized formatting.

Turn on Search Fields as Formatted by clicking the check box next to it.

This setting doesn’t work with Match Case, so if Match Case is checked, Search Fields as Formatted appears dimmed. In that case, uncheck Match Case to bring back the Search Fields as Formatted check box.

Most of the time, this option doesn’t make much difference. In fact, the only time you probably care about this particular Find option is when (or if) you search many highly formatted fields.