How To Use Filter By Form In Access

Sometimes yous need to see a group of records that shares a mutual value in one field in Access. Perhaps they all list a particular city, a certain job title, or they're all products that have the same cost. Always willing to assistance, Access includes a special tool for this very purpose: the Filter command.

Filter uses your criteria and displays all matching records, creating a mini-table of simply the records that see your requirements. Information technology'southward like an instant query without all the piece of work and planning. Of course, it's not as flexible or powerful as a query, but it's all you demand when you're looking for a fairly simple answer.

The Filter tool appears in the Sort & Filter section of the Ribbon's Home tab, and you have the following choices for a uncomplicated filter:

Filter
Selection
Avant-garde Filter past Grade
Advanced Filter/Sort
Toggle Filter

Each type of filter performs the same basic function, simply in a slightly different way. Advanced Filter/Sort pick, found past clicking the Advanced button, opens a window that really has you building a query — selecting tables and fields to filter, setting up criteria for the filter to utilize while information technology'south finding specific records, that sort of thing.

Filters work in tables, forms, and queries. Although y'all can utilise a filter to a study, filtering reports can be a daunting task. Of course, what you read hither tin can exist applied to that process, should y'all desire to effort it on your own. And what you learn to apply to a table can also be practical when you're working with queries and forms.

Filtering by a field'southward content

The primary Filter command enables y'all to filter your records then you view just records that meet specific criteria. Suppose, for example, that you want to see all records for people living in a particular urban center. Here's how to do information technology:

In your tabular array of involvement, click the small triangle on the field proper name for the field you desire to filter (City in this case).

Access displays a pop-up menu.

Don't right-click the header at the top of the cavalcade (where information technology says City). Right-clicking there displays a different popular-upward bill of fare filled with wonderful things you tin can do to that column of your tabular array.

Filtering a single field based on that field's entries.
If you desire to omit some entries from your filter, remove their check marks in the popular-upwards menu.

You lot tin either
- Remove bank check marks from private entries.
  
  or
- Remove the Select All check marking to uncheck all the items.
Uncheck everything so you can easily check but those entries whose matching records you desire to meet. With all the entries checked, you come across all the records.
Place check marks next to those entries yous desire to use in constructing your filter for the field.

Access searches the selected field and displays only those records that meet your Filter criteria.
Click OK.

All the records meeting the criteria set (by virtue of the items you checked) are displayed. This might exist several records, a whole lot of records, or just one.

To jump back and run into all the original (unfiltered) records, you can either

Click the Toggle Filter button in the Sort & Filter department of the Ribbon to remove the last filter applied.

or
Click the field proper noun's tiny triangle again and choose Clear Filter From Field Name.

If you lot don't remember which fields yous filtered, you lot can tell them past the small funnel symbol that appears side by side to the field name in Datasheet view.

The entire tabular array, full of records, returns to view.

Filter by selection

The Option command is the easiest of the Sort & Filter commands to apply. It assumes that you've found 1 tape that matches your criteria. Using the Option filter is a lot similar grabbing someone in a crowd and shouting: "Okay, everybody who'due south similar this guy here, line up over there."

For example, imagine yous want to find all the volunteers who live in Landisville. You tin apply the Option filter in this style:

Click the field that has the information y'all desire to match.

In this instance, information technology'due south the Urban center field.
Scroll through the listing until you find the field entry that will serve as an appropriate instance for your filter.
Click to select the value you lot're searching for, right-click the cell containing the option, and so cull Equals Whatsoever. (In this instance, it would be Equals Landisville.) You can besides select the cell and then click the Option button on the Habitation tab (in the Sort & Filter section) to access the same list of choices.

Access immediately displays a tabular array containing simply the records matching your selection.

Access shows merely those records matching the Filter by Pick criterion.
Click the Toggle Filter button on the toolbar after you finish using the filter.

Your table or form returns to its regular display.

At this stage of the game, you lot may want to save a listing of everything that matches your filter. Unfortunately, the Filter'southward simplicity and ease of apply now come dorsum to haunt you. To permanently record your filtered search, y'all have to create a query.

Yous can also use the Filter bill of fare that shows sorting options and the list of checked field values. When you display the pop-up bill of fare, unclick Select All and and then check the value you want to look for. And so choose Text Filters from that aforementioned pop-upward card. The aforementioned listing of options (Equals, Does Not Equal, Begins With, and so on) appears every bit a fly-out menu, and you can brand your choice there.

Filter past Course

You can tighten a search by using additional filters to weed out undesirable matches, but that takes a ton of extra endeavor. For an easier way to isolate a group of records according to the values in more than one field, try Filter by Class.

Filter by Course uses more ane criterion to sift through records. In some ways, it's like a simple query. It'south so similar to a query that you can even save your Filter by Class criteria every bit a full-fledged query!

Suppose, for example, that yous need a listing of all the employees at your visitor who work in a certain department and have a item title. Y'all can perform ii Pick filters (on the Department and Job Title fields, using the employee database every bit an example) and write down the results of each to get your listing, or you can practice just one search with Filter by Grade and see all the records that run into your criteria (based on their entries in multiple tables) in a single stride.

To utilise Filter by Form, follow these steps:

On the Ribbon's Home tab, click the Avant-garde button in the Sort & Filter section.

A menu appears.
Choose Filter past Form from the carte du jour.

The tabular array is replaced by a single row of cells, i under each field header in your tabular array.
Click in the empty cell beneath field proper name for the kickoff column that you want to filter.

Utilise the scroll bars to bring the cavalcade onscreen if information technology's off to the right and can't exist seen.

The downward arrow jumps to the cavalcade you click.
- Normally, Access shows a down-arrow push next to the first field in the table.
- If you previously used a Filter command with the table, Access puts the down-pointer button in the last field you filtered.
  
  Filter by Form offers a grid and drop-down lists to set criteria for each field.
Click the downwards pointer to see a list of values that the field contains.

The drop-down listing shows all unique values in a field.
In the list of values, click the value that you want to utilize in this filter.

For instance, if you select Landisville from the driblet-down list in the City field, "Landisville" moves into the City column. Access adds the quotes automatically — one less item that yous have to call up!
To add another filter option for the called field, click the Or tab in the lower left corner of the table.

A new Filter by Class window appears, letting you add an alternate search status. Access also offers an Or tab to the lower left corner of the display.

The Filter past Grade command likes to answer simple questions, such as "Prove me all the volunteers who alive in Lancaster." It also provides answers to more complex questions like "Show me all the records containing Th in the AvailableDays field and who accept Landisville in the Urban center field," and information technology performs both tasks easily.

Asking a more complex question (such as "Show me all the volunteers in Lancaster who similar fundraising") requires a query.

Click the Or tab to further ascertain the criteria.
For each additional field you want to filter, click the Or tab and repeat Steps 3 through 6.

In this example, the second field to be filtered is VolunteerInfo, and Fundraising was typed into the field's box.
When you finish entering all the criteria for the filter, click the Toggle Filter button.

Access finds all the volunteers who like fundraising based on filtering ii of the table'south fields.

A final thought about Filter by Course:

Although yous can get fancy by calculation Or searches to your eye'south content, keeping rails of your creation gets tough in no time at all. Before yous go too far, remind yourself that queries work improve than filters when the questions get complex.

When yous finish piddling with your filter, click the Toggle Filter push. At that betoken, your table returns to normal (or at least as normal equally data tables ever get).

Unfiltering in a grade

What do yous exercise when you enter criteria past fault? Or when you determine that y'all really don't want to include Lancaster in your filter correct after you click Lancaster? No trouble — the Articulate Filigree command comes to the rescue!

When you click the Clear Filigree control (found in the Sort & Filter section'south Advanced card), Access clears all the entries in the Filter by Form grid and gives you lot a overnice, clean place to offset over once more.

Filter by excluding selection

The Selection filter tin also exist used to exclude sure records. This works great for times when you want to briefly hide a bunch of records that all share a unique attribute (a particular state, urban center or zip code).

Here's how to make the Choice filter exclude records for yous:

Scroll through the table until you notice the value you want to exclude.
Correct-click the field containing the value and then choose Does Not Equal ______ (where the blank represents the value you lot've correct-clicked) from the carte that appears.

Those records matching the value you've chosen exit of the fashion then you can concentrate on the records that really involvement yous.

With one click, Access hides all volunteers whose Status field value is Prospective.

You tin can also click the Pick button (in the Sort & Filter section of the Domicile tab) and cull Does Not Equal from the menu there. You'll also observe the Contains and Does Not Comprise commands. These are handy for culling records that accept something in mutual, such as a particular word or number inside them. Yous can also combine filters, excluding not but (for example) volunteers who are Prospective, merely the Prospective volunteers who live in a certain City or Country.