Search options in Microsoft Access

[Find dialog in a Microsoft Access database] Use the Find button (or the CTRL+F shortcut) to find data in a table or in a form. This dialog will appear to let you choose what to search for and how to search for it.

Find What:

Enter the text or numbers you're searching for here.

Search:

This setting determines how much of the table will be covered by the search.

  • Up searches the records before the current record.
  • Down searches through the records after the current record.
  • All makes the search go through the entire table.

Match:

This setting determines how much of the field is going to be searched.

  • Start of Field is the fastest type of search. It only looks at the first few characters of each record.
  • Whole Field looks for a record which is an exact match for the entire expression.
  • Any Part of Field is the most flexible type of search but also the slowest. It will find any record which contains the expression anywhere in the field.

Match Case

Tick this if you want Access to take account of the case of the letters you're looking for. It's best to leave this clear unless you are absolutely sure that the data has been entered in the correct case. Addresses in particular can be a mixture of 'UPPER', 'lower', and 'Proper Case' entries.

Search Fields As Formatted

This box is greyed unless you have ticked Search Only Current Field. It becomes important when you have a field that is being displayed in a special format. For example, a search for '40' will find a price of '£40' if this box is clear. You would have to search for '£40' if the box were to be ticked.

Search Only Current Field

The search will be faster if you tick this box because Access will be able to concentrate on single field and may be able to use an index rather than having to use brute force techniques to examine every field.

Presetting the options

If you select Options on the Tools menu and select the Edit/Find page then you can set the default type of search.

  • Fast Search - equivalent to 'Whole Field'.
  • General Search - equivalent to 'Any Part of Field'
  • Start of Field Search - equivalent to 'Start of Field'.

Note that the option you choose won't take effect until the next time that you start Access.