Finding Records in Your Access 2003 Tables

When you want to track down a particular record, creating a query is overkill. Fortunately, Access 2003 has a quick way to find one specific piece of data in your project's tables and forms: the Find command.

Find is available both on the toolbar and through the main menu. (Choose Edit --> Find. Keyboard-oriented folks out there can press Ctrl+F.) Access 2003 doesn't care which way you fire up the Find command — it works the same from either avenue.

Although the Find command is pretty easy to use on its own, knowing a few tricks makes it do its best work. After you know the Find basics (covered in the next section), you can fine-tune the Find command by using the tips in the section called, "Tuning a search for speed and accuracy," later in this article. That section shows you how to tweak the Find settings for more detailed search missions.

Finding first things first (and next things after that)

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

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

Yes, Find works in both datasheet view and with Access forms.

2. Click the field that you want to search.

The Find command searches the current field in all the records of the table, so make sure that you click the right field before starting the Find process. Access 2003 doesn't care which record you click — as long as you hit the right field, Access is happy. (And it's important to keep your software happy!)

3. Start the Find command by clicking the Find toolbar button (the one with the binoculars on it) or choosing Edit --> Find.

The Find and Replace dialog box pops into action.

4. Type the text you're looking for into the Find What box, as shown in Figure 1.

Take a moment to check your spelling before starting the search. Access isn't bright enough to figure out that you actually mean hero when you type zero.

>

Figure 1: The Find and Replace dialog box gets ready to do its stuff.

5. Click Find Next to begin your search.

Before you can count to one by eighths, the Find command tracks down the record you want, moves the cursor there, and highlights the matching text. If Find doesn't locate anything, however, it laments its failure in a small dialog box.

6. If Find didn't find what you were looking for:

• a. Click OK to make the dialog box go away.

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

• You may also want to check the special Find options covered in the next section to see whether one of them is messing up your search.

• c. Click Find Next again.

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 works its way down to the record you want or tells you that it's giving up the search.

Tuning a search for speed and accuracy

Sometimes, just providing the information in the Find What box isn't enough. Either you find too many records or the ones that match aren't the ones that you want. The best way to reduce the number of wrong matches is to add more details to your search.

Precise adjustment makes the pursuit faster, too.

Access offers several tools for fine-tuning a Find. Open the Find and Replace dialog box by clicking the Find button on the toolbar or by choosing Edit --> Find. The following list describes how to use the various options:

• 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, change the Look In setting to the Table option, as shown in Figure 2.

>

Figure 2: To search the entire table, change the Look In setting.

• Match: Access makes a few silly assumptions, and this setting is a good example. By default, Match is set to Whole Field, which assumes that you want to find only fields that completely match your search text. The Whole Field setting means that searching for Sam doesn't find fields containing Samuel, Samantha, or Mosam. Not too bright for such an expensive program, is it? Change this behavior by adjusting the Match setting to Any Part of Field, which allows a match anywhere in a field (finding both Samuel and new sample product), or to Start of Field, which recognizes only a match that starts from the beginning of the field. To change this setting, click the down arrow next to the field (see Figure 3) and then make your choice from the drop-down menu that appears.

>

Figure 3: Look insideall those little fields by 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 Search option. Search tells the Find command to look either

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

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

Tune your Search settings by clicking the down arrow next to the Search box and choosing the appropriate choice from the drop-down menu.

• Match Case: Match Case requires that the term you search for be exactly the same as the value stored in the database, including the case of the characters. In other words, if the database contains the oddly capitalized name SmItH, Match Case finds that value only if you search for the name using exactly the same odd capitalization (in this case, SmItH). Match Case works very well when searching your database for names.

• Search Fields As Formatted: This option instructs Access to look at the formatted version of the field instead of the actual data you typed. Limiting the search in this way is handy when searching 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 Search Fields As Formatted.

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

If your Find command isn't working the way you think it should, check the options in the preceding list. Odds are that one or more of these options aren't set quite right!

>
dummies

Source:http://www.dummies.com/how-to/content/finding-records-in-your-access-2003-tables.html