One of the best aspects of a database is that it centralizes all of the company’s important data in one place, but the obvious drawback is that it makes locating specific data considerably more difficult. Microsoft Access 2013 includes filters that pull specific information instead of users having to muddle through countless records. There are several different ways to run filters, so users can customize the function to meet different needs.

Users can establish a filter for terms, phrases, numbers, dates, locations, or even with wildcards. The data can be pulled from forms and tables, but perhaps one of the most useful tools is the ability to run a filter that considers multiple fields.

Filtering is one of the reasons why it is very important to establish terms and key information before getting started. For fields that are likely to be key in filters, establish a format, such as MM/DD/YYYY for dates so that employees will fill it out 05/04/2015. Drop down menus are also a great way to make sure key words are used consistently.

Determine Whether You Should Use a Query or a Filter

Queries and filters provide different types of target searches for data, and they require different levels of effort. Queries require time to consider how to design them and can pull in data from different Access objects. This means they can review multiple scenarios at the same time, giving the user the ability to look at alternative criteria simultaneously.

Filters are generally for quick data pulls that the user may or may not need again. They focus on a single object or a specific set of information, so they provide a more focused look at existing data. Users can also use them with different sets of criteria, but these must be pulled individually instead of displaying simultaneously.

Filter by Selection, Word, Phrase, or Number

Because filtering is meant to be a quick way to get data there are a number of different ways to apply a filter. There are several types of filters:

• Equals/Does Not Equal – The filter looks for only the specified data to either include or exclude it. Text or numbers must match the entered information exactly to be pulled or excluded.
• Begins With/Does Not Begin With – The filter includes or excludes for all entries beginning with the specified data.
• Contains/Does Not Contain – The filter includes or excludes any records that contain the specified data.
• Ends With/Does Not End With – The filter includes or excludes for all entries ending with the specified data.

Here's how to create a filter:

1. Highlight the word, number, or cell for the filter.
2. Click on Home > Sort & Filter, then Selection from the drop down menu.
3. Determine the filter type

The following can be used for text or numerical values.

1. Click on the drop-down menu beside the header of the row on a table you would like to filter.
2. Remove the check next to (Select All), and click on the criteria you want to add.
3. Select Text Filters,
4. Select the type from the menu.

Use of the Wildcard in Filters

Like most Microsoft products, the wildcard is an especially helpful way of limiting data even if the user is not entirely certain of phrase, location, or time period. The wildcard is the asterisk (*). For example, if an employee wants to run a search for data for all sales, it is likely that the sales will have been entered by different names, such as Back to School Sales, Holiday Sales, or Spring Sales. By entering “*Sales” in the Description field, the filter will return all data that has the word Sale in the description.

Filter Multiple Fields and Criteria

Filtering over multiple fields and criteria similar to other filters, but the user gets a much wider set of data. The best way to filter over multiple fields and criteria is with the Filter by Form feature. This lets the user work across multiple fields or criteria while defining the filters for the table.

There are two options, And and Or.

• And - The filter returns data that meet all of the specified criteria, such as purchases in October in Atlanta.
• Or – The filter will return all values that meet at least one of the specified criteria

Users can use both And and Or. For examples, if a company needs to know how much it spent over the summer and how much it spent in New York City and Seattle, the filter is set so that New York City and Seattle are included for location or June, July, and August for time.

1. Go to Sort & Filter > Advanced > Filter by Form.

2. Select the field beside the criteria, and then select the value to filter from the drop down menu.

3. Repeat step 2 to apply other criteria to the filter. This automatically applies and to the criteria.

4. To select use OR for the criteria, select OR in the bottom-left portion of the window.

5. Repeat step 2 in the Or tab.

6. Click Filter.

Filter Basics: Switching Filters Off, Saving, and Auto Applying

Once filters have been run, users can easily switch them off by clicking on the Filter button on the navigation bar. Or it can be deleted by right-clicking and selecting Clear filter or Clear all filters.

Any filters not cleared save automatically for next time, so that when the table, form, report, or query are opened the user only needs to view the filter menu (clicking on the Filter button if it is turned off). To automatically apply the filter when the object is opened again, click on FilterOnLoad for the filter’s property.

Filters can also be saved as queries by clicking Home > Sort & Filter > Advanced > Advanced Filter/Sort, then make adjustments and Save As Query. This is particularly useful if a user finds that a filter is needed on other objects.