MS Access Filters & Find Functionality: Searching Tables Using MS Access Find and Filter Options

So hopefully the previous articles will have broadened your understanding of relational database design and table linking. In conjunction with this comes referential integrity for primary and foreign keys and the corresponding affect cascade update and cascade deletes have on database records. Once this is all setup, fine tuned and you are comfortable with the results being produced, then and only then can you see the real benefit when searching tables.

Searching Tables in Microsoft Access

There are two methods of searching data in your database tables: via Find and Filter.

Using Find (and Replace) to search tables

The find feature allows you to Find or Replace a record based on the criteria ‘string’ you enter, and allows you to jump to the next occurrence of that said find ‘string’ depending on the search you performed.

1. Position the cursor in a field or column that you wish to search on.

2. Click the find button (binoculars) or choose Edit – Find and the Find dialog box will be displayed.

3. In the Find What box enter the text you wish to search on. This will be understood as a string. The Look In box allows you to specify the field you wish to search either uniquely or in the table as a whole. The Search box allows you to search in various directions throughout the whole of the table. The Match box allows you to search the whole field, any part of the field or the beginning of the field. The Match Case tick box allows you to be case specific with your search. The Search Fields As Formatted tick box allows you to search based on specific display formats. Searching in this way is very powerful because you can drill down to specifics such as formatted dates and times but it is also much slower than the traditional find string.

4. Choose any of the options you wish and click Find Next. This will return the first matching record which has the related ‘find’ string. Click Find Next again to see the next occurrence.

Using Filter to search tables

Filtering records in Access means temporarily omitting information from your view that you do not require. This is pretty much the same as running a simple query and as common as it is, many people in many organizations actually have query’s setup with this exact search request in mind – which is bad practice as a simple filter would be much easier to manipulate and far less time consuming to run.

It is very good practice to use filters when you want to extract records for information gathering purposes or want to sort data into an order that you would like across multiple fields.

The two filter options that are available to you using a table or form are

Filter By Selection

Used to filter data using the field you currently have the cursor ‘active’ in. It is similar to running a simple query for related fields but provides immediate results.

To Filter By Selection:

1. Click the field that you want to filter.

2. On the toolbar click the Filter By Selection button. The records that match the data you currently have the cursor in will be displayed.

3. The filter indicator will be prominently displayed in the status bar as a reminder that the filter is on and in use. To remove the filter click on the Remove Filter sort button.

Filter By Form

Uses a blank version of the table or form to allow you to quickly search on any number of fields by choosing values that exist in the said fields. This is a highly powerful and expedient search tool. Certainly one that can eliminate the need for all those query’s you have hanging around!

To Filter By Form:

1. On the toolbar click the Filter By Form button. The view changes to show a table with no corresponding data items.

2. Click a column to show a local list of the values that can be used to make the filter selection.

3. Make various selections from the multiple columns that can be filtered from the local list that is provided. This is powerful because you can use a second or more columns to filter with greater depth and efficiency.

4. Click the Apply Filter button and records matching your data parametres will be shown.

5. As above, to turn off the filter click the Remove Filter button.

Saving your search

If you find that you are performing filter by form searches on a regular basis it may be worth saving the criteria you are entering, or better still saving the more complicated multi-field filters as query’s.

1. In the Form view or table datasheet view choose Records – Filter – Advanced Filter/Sort. Access will launch the query dialog.

2. Choose File and select Save As Query.

3. Enter a name which is appropriate to this filter and click OK.

Related Reading:

>>> Microsoft Access: Join Properties and Join Types

>>> Microsoft Access: Referential Integrity

>>> Microsoft Access: Setting up relationships between tables

>>> Microsoft Access: Understanding Lookup Fields

>>> Microsoft Access: Understanding Data Types

>>> Microsoft Access: Designing a Relational Database