Home > Jan's CompLit 101 > Working with Databases > Basics > Access Objects > Sort & Filter

Jan's Working with Databases

Basics: Access Objects: Sort & Filter

As you accumulate more records, you will sometimes want to sort the records into a different order or to filter the records to see only the ones that match certain rules, called criteria.

For example, you might want to sort the records by AmountPaid or ItemName or Category.

You might want to filter the records to see only records for the year 2012 or where Cost is more than $500. So many choices!

Example for illustrations:

Star Wars Collectibles table in the starwars.accdb database

You can download the database to follow along with the illustrations below. If you mess it up, just download again!

Star Wars Collectibles - original datasheet (Access 2010)

Original Datasheet, with no sorting or filtering

Sorting

What sorting does

Sorting puts the records in order based on the field that you select, either ascending or descending order.

Sort a text field: Alphabetical order.
Ascending: a, b, c... Descending: z, y, x...

Sort a number field: Numerical order.
Ascending: 1, 2, 3, ..., 100, 101, 102.... Descending: 102, 101, 100, ..., 3, 2, 1

Sort numbers that are in a text field: Alphabetical order like 1, 10, 100, 2, 20, 200... instead of numerical order.

How to sort:

Button: Click in the field you want to use to sort and
then click on a Sort button on the Home ribbon tab.
Context Menu: Right click in the field you want to use to sort and select a sort command.
In the context menu, for different data types the symbols are the same but the words for the commands change. This can be more helpful than the fixed text that shows on the ribbon button.

Star Wars datasheet - sorted smallest to largest - YearMade (Access 2010)

Datasheet sorted on YearMade, smallest to largest
ItemID no longer in numerical order

How to tell a datasheet is sorted:

The Sort Ascending or Sort Descending button is highlighted on the Home ribbon tab.
The color of the highlight varies with the version.
A column heading has a sort symbol with the arrow, or .
The button Remove Sort is available.
When records are in the default order, the button is grayed out.

In one sense, a datasheet is ALWAYS sorted. By default the records show in the order in which they were entered. If there is an AutoNumber primary key field, then that field is in numerical order. An easy way to get back to the original order is to sort on the primary key field, if your table has an AutoNumber primary key.

How to remove a sort:

Button: On the Home ribbon tab, click the Remove Sort button.

One sort active at a time
Sorts do not build on each other. A new one replaces the previous one. To sort on more than one field at once, you must use an advanced filter or a query.

Saved with table
If you save your table while it is sorted, the sorting order is saved.

Filtering

What a filter does

A filter shows only records that match the criteria that you chose.

A simple filter looks for records that match one value in one field. An advanced filter can have several criteria in multiple fields, and even combine those with sorting.

The symbol for a filter is a funnel, .

Multiple filters are nested
If you apply a filter to your datasheet and then apply a new filter, you will be filtering the already-filtered records. This will likely not give you the same results as using your new filter by itself! Be sure to remove the first filter if you want to start over. Also remember that the order you apply the filters can often make a big difference.
Saving a filter
When you filter a table and then save the table, the last filter you used is saved with the table. But, when you open the table again, the filter is not automatically applied.
Apply the last used filter: Click the Toggle Filter button on the Home ribbon tab or the Unfiltered or No Filter button on the Navigation Bar for the datasheet. These buttons are not available until after a filter has been created and applied.

You can save a filter as a query to use later.

In the illustration below the datasheet is filtered to show only items where the Condition field has the value "Excellent". Only 3 of the 14 records in the table match this single criterion.

The results are also sorted on the field EpisodeID in ascending order.

Datasheet after changing layout, filtering, sorting

After filtering. Several ways to tell that the datasheet is filtered.

How to tell when records are filtered:

Datasheet Navigation Bar says so: Filtered 'Filtered' is a toggle button which will reapply the last filter used or remove the filter. That filter is not deleted however.	Toggle Filter button on the ribbon is highlighted.
Status Bar says so: Filtered	Column heading shows Filter icon

Ways to Filter:

Filter By Selection
Filter Excluding Selection
Filter By Form
Advanced Filter grid

Filter By Selection

The easiest way to filter is by selection.

Button: Click in a cell with the value that you want to see in the results.
Then, click on the Filter By Selection button on the Home ribbon tab and then on the first item in the list, Equals <what is in the cell>.

Or, click the arrow on the button to open a list of options.
Context Menu: Right click on the cell with the value you want to use to filter and choose a filter from the same list.

The datasheet refreshes and shows records that match the filter; the other records are hidden. Simple - for a simple filter on one field.

Filter By Form

When you want to filter records based on a combination of criteria on two or more fields, you must use a more advanced method. You cannot use this form to sort. The form is just a datasheet with only one row. You can enter criteria for one or many of your fields in this row.

Click the Advanced button on the Home ribbon tab in the Sort & Filter tab group.
A menu opens.
Click on Filter By Form.
A new window appears with one row, showing each of the fields from the table.

Each field has a drop list of possible values. You can only pick one value for the field. "Null" and "Is Not Null" are the only choices for some data types: memo, hyperlink, Yes/No, or OLE object.

Filter By Form: Filters using a single value for each of two fields
EpisodeID = A New Hope 1977 and Condition = Good
Choose the value you want to see in the results for each field that you want to use. The results must match all of the choices at once. So in the illustration, a record must have a matching EpisodeID AND must match the condition, 'Good'.
Click the Toggle Filter button on the Home ribbon tab, Sort & Filter tab group, to apply the filter.

Filter results

Filtered datasheet. Only the records that match all criteria on the form's 'Look for' tab.

When you want 'this' OR 'that': At the bottom of the Filter By Form window are two tabs: Look for and Or. The choices on any one tab work as AND so any result must meet all of the criteria on that tab. But... you can make other choices on the second tab, so that the filtered results must match what you chose on the first tab OR what you chose on the second tab. As soon as you select the second tab, a third blank one is created. It can get complicated!

Strange Results: If your results do not show what you expected, check the form to see if you have choices selected on multiple tabs or for a field that is out of view. Delete any values that should not be in the filter.

Advanced Filter Grid: Combination Sort and Filter

The Advanced Filter/Sort grid lets you combine sorts and filters and use multiple fields.

Click the Advanced button on the Home ribbon tab in the Sort & Filter tab group,and then Advanced Filter/Sort.
A new tab appears next to the datasheet tab. It has a top and bottom pane. In fact, this is like a query's design view.
Current filter criteria and sorting orders will show in the grid in the bottom pane.
You can drag any other fields that you want to use for sorting or filtering from the list in the top down to the grid below.
Sort row: Set sorting on a field by choosing "Ascending" or "Descending" or "(not sorted)" underneath the field name.
Criteria rows:
- Enter a value that you want to match in quotes, like "Excellent".
- Enter a values that you do NOT want to see in like: NOT "6.99"
- Enter Is Null to find empty values.
- Values on the same Criteria row work together as AND.
- Values on separate rows work as OR.

The illustration shows a filter that will show any item that is from episode "A New Hope 1997" and is in 'Good' condition OR is from "The Phantom Menace 1999" and was a gift.

Using quotes around values: Access can usually figure out what you meant if you do not type the quotes around criteria values and will add them for you. But, sometimes it gets confused. It is a good idea to get into the habit of typing those quotes yourself.

Delete a Filter

It is not obvious how to completely delete a filter instead of just toggle it off.

In the Advanced view, from the ribbon, Advanced button > Clear Grid.
All criteria and sorts are removed but the filter window is still open.
Click the Toggle Filter button on the ribbon.
You are returned to the datasheet will all records showing.
The Toggle Filter button is no longer available, which shows you that the filter is truly gone. The filter tab is still open so you can create a new filter in it.

When You Forget What the Filter Does...

Look at the Advanced Filter/Sort. All the sorts and filtering criteria will be there, no matter what method you used to create the filter. This can be very useful! It is very easy to forget what the last filter was. Creating a new filter may just filter the already-filtered results. That's fine if it is what you meant to do. It can be disastrous if you did not realize what had happened.

Step-by-Step: Sort & Filter a Table

What you will learn:

to add records to an existing table datasheet
to sort a table datasheet on one column
to apply Filter By Selection
to apply Filter By Form
to use Filter By Form with multiple criteria: AND, OR
to use Advanced Filter/Sort
to remove a filter

Start with: ,, Access open but no database open,mytrips-Lastname-Firstname.accdb from previous lesson

Add More Records

It's not much fun to sort and filter with just 3 records. You need to add some more trips.

Back up this database now so you will have a good copy to use if you need to start over. This works well while the database is small.
Access 2007: > Manage > Back Up Database

Access 2010: File > Save and Publish > Back Up Database > Save As button

Access 2013: File > Save As > Back Up Database > Save As button

Open the Trips table and add the following trip records.
The PhotoLink field is empty for these records.
You can type the info in or copy one value at a time and paste directly into the datasheet. Notice how Access reformats the dates automatically to the format in the field's properties.

Field	Value
TripName	Family Reunion - 2003
PhotoLink
DateStarted	5/31/2003
DateEnded	04-Jun-03
Description	Drove to Birmingham and stayed at Grandmother's house. Lunch at the park.
Cost	$250.00
Travel Agent	none
Diary	Arrived just in time for lunch. 50 family members present plus some friends and soon-to-be family members. Played baseball, sort of, after lunch. Unusual "family" rules involving big people running bases for little people or little people running bases for big people.

Field	Value
TripName	Hilton Head
PhotoLink
DateStarted	28-Jun-96
DateEnded	06-Jul-96
Description	Drove to Hilton Head. Stayed at friend's time share. Beach privileges.
Cost	$1,000.00
Travel Agent	none
Diary	Were beach bunnies for 2 days. Played golf and did jet skis. Climbed the lighthouse. Saw natural history museum. Bought gorgeous shells.

Field	Value
TripName	New Orleans Convention
PhotoLink
DateStarted	May 5, 1995
DateEnded	May 15, 1995
Description	Stayed at New Orleans Hyatt. First 3 days for High Reaches Club.
Cost	$2545
Travel Agent	Through company having the convention
Diary	Arrived late and luggage not in sight for 2 days. Did get it in time for the banquet. Took special tour of Louisiana plantation homes and the aquarium. Love the jellyfish in black light and the fancy seahorses.

Trips table with new records

Datasheet after entering 3 new records.

Now we can try some sorting and filtering.

Datasheet: Sort

To start with, records are all listed in the datasheet in the order in which they were added, so the TripID field will be in numerical order. It is an AutoNumber field so it automatically numbers each new record. You can choose to sort on another field.

Click in the TripName column somewhere.
Click on the Sort Ascending button on the ribbon.
The records rearrange so that the TripName column is in alphabetical order.

Sorts are saved: If you save the table at this point, it will be sorted this way when you open it later.
Experiment: Sorting
- Sort each column, both ascending and descending.
- Which columns cannot be sorted? (The sort buttons are grayed out.)
  In Datasheet View, each new sort replaces the old one. You cannot use a multiple sort directly in the Datasheet view, like LastName, then FirstName, then MiddleName.
  
  To sort on more than one column at a time, you must use a query or the Advanced Filter grid, which you will do shortly.
Click the button on the Home ribbon tab:
Access 2007: Clear All Sorts
Access 2010: Remove Sort
Access 2013, 2016: Remove Sort

The records return to the original display order with TripID in numerical order.

Datasheet: Filter By Selection

Another way to change the datasheet display is to apply a filter. This hides some of the records. Filtering is more fun with more records than you have here!

When are your records filtered?
You must be alert as to whether your datasheet is filtered or not.
Look at:
Navigation Bar
Status Bar

Ribbon
Column Heading

Click in the TravelAgent column in one of the cells with the value "none".
Click on the Selection button .
A list opens.
Click on Contains 'none'.
Three records match your filter.
Notice that beside the Navigation Bar is the word "(Filtered)".

Four ways to know the datasheet is filtered: TravelAgent=none
Inspect the window for clues that this datasheet is filtered.
Can you find all four? Toggle button, Navigation Bar, Status Bar, Column Heading.
Click on the Toggle Filter button on the Home ribbon tab, .
All records reappear.
Inspect the window for clues about whether filter is available or not.
There are two, which tell us that a filter is available but is not currently active:
- Toggle Filter button is available but is not colored.
- Navigation Bar shows 'Unfiltered' .

Datasheet: Filter By Form: Is Not Null

You can use a filter to check for records that have blank fields, or for records that have values in a particular field. Filter By Form makes either one easy to do.

Click on the button Advanced to open its list and then on Filter By Form.
A one row form appears. It shows the single criterion for the previous filter, TravelAgent = "none.
Notice that there are two tabs at the bottom, Look for and Or. For this section you will work on the Look for tab.
Press the DELETE key.
Like "none" vanishes from the form.
Click on the field PhotoLink. A drop list appears. The actual values in the records do not show, only "Is Null" and "Is Not Null". This is true for several kinds of fields: memo, hyperlink, Yes/No, OLE object, and Attachment.
Click on "Is Not Null".
Click on the Toggle Filter button.
Only two records have a link entered.
Click on the highlighted Toggle Filter button.
All six records reappear and the Toggle Filter button loses its color.

Datasheet: Filter By Form: Multiple Criteria (AND)

When you want the results to satisfy multiple criteria at the same time, you can use Filter By Form to choose several values on the Look For tab.

Open the Filter By Form again.
The previous criterion is still in place: PhotoLink = "Is Not Null"
Change PhotoLink to "Is Null".
In the Cost column, instead of choosing a value, type >1000 .
You now have set 2 criteria for the filter. You are looking for trips that cost more than $1000 and which do not have a link to photos.
Click on the Toggle Filter button.
Two records meet the criteria - Alaska and New Orleans.
Click on the Toggle Filter button.
All six records reappear.

Datasheet: Filter By Form: Multiple Criteria (OR)

When you want to see a combination of results that meet one set of criteria or meet a another set, you can set the different criteria on different tabs.

Open Filter By Form again.
Leave the choices on the tab Look for.
Click on the Or tab at the bottom of the window.
Another Or tab appears. No choices have been made on these tabs yet.
Select PhotoLink = Is Null and TravelAgent = "none"
Click on the Toggle Filter button.
Four records meet the criteria set on the two tabs:
PhotoLink = Is Null and Cost > $1000
Or
PhotoLink = Is Null and TravelAgent = "none"

The results are shown in the original order, not by which set of criteria they met.

The Alaska and New Orleans trips meet the criteria on the Look for tab.
The Family Reunion and Hilton Head trips meet the criteria on the Or tab.
Click on the Toggle Filter button.
All six records reappear.

How to know when a filter has multiple criteria:
Look at the tabs at the bottom of the Filter By Form window. If there are only two tabs, then all of the criteria are on the Look for tab. If there are more than two tabs, then there are criteria on all but the last tab. The filtered records must meet the criteria on one of the tabs.

Datasheet: Advanced Filter/Sort

To combine sorting with filtering, you can open an Advanced/Filter Sort grid. This looks a lot like a Query Design View, which you will work with in the next lesson.

On the ribbon, click on the button Advanced and then on Advanced Filter/Sort...
The grid appears with the criteria and sorting from the last filter that you applied displayed. Notice that the filter has its own tab in the Object window. Each time you click Advance Filter/Sort, a new tab will appear.

The grid includes only the fields that were used in the last filter. There are two criteria rows, which correspond to the two tabs in the Filter By Form from the previous section. This view has an advantage over Filter By Form. You can easily see that there are two sets of criteria involved.

If you had applied a sort, it would show on the Sort line of the grid.
Hover over the column PhotoLink until the mouse pointer changes to the Select Column shape, .
Drag to the right to select all three columns with field names and criteria.
Press the DELETE key.
The grid is now blank.
Drag the field name DateStarted from the list at the top.
The mouse pointer changes shape to the dragging shape .
Drop it onto the grid in the first column.
Click in the Sort row under the field DateStarted.
An arrow appears at the right side of the cell.
Click on the arrow.
A list of choices appears.
Click on Ascending.
In the Criteria row in the first column, type <1/1/1999 .
Press the TAB key to move out of the cell in the grid.
Access automatically revises what you typed to correct format for a date in an expression: <#1/1/1999# in Access 2007 and 2010 and <#01/01/99# in Access 2013 and 2016. Very helpful!

Your filter will now look for records with a DateStarted before January 1, 1999, and it will sort them in chronological order.
Click on the Toggle Filter button.
The datasheet is now filtered to show the three records that have a DateStarted before 1/1/1999. The records are sorted on the DateStarted field.
Open the Advanced Filter/Sort again.
Click in the second column in the Field row.
A down arrow appears at the right edge of the cell.
Click on the arrow to open a list of all of the table's fields
Click on Cost.
In the Criteria row under Cost, type >=1000
Since both criteria are in the same row, your filter will look for records that satisfy both: Cost greater than or equal to 1000 and started before Jan. 1, 1999.
Click on the Toggle Filter button.
Only two records meet both criteria.

Remove Filter

It is easy to toggle a filter off but that does not get rid of the filter entirely. It's still in the background, ready to be used again.

Click the Advanced Filter button again.
In the menu, click on Clear All Filters.
Inspect the datasheet.
All of those clues that the datasheet is filtered are gone.
But the records are still sorted. Unexpected!
Click on the button Remove Sort.
Now the datasheet is back to the original order and the Toggle Filter and Remove Sort buttons are grayed out. That means no sorting and no filters are available. Success!
Close the table datasheet.

If prompted, choose to save changes.

Alternate method: Click on the title of the table in filter, TripsFilter1. The grid shows in the window. Click on Advance Filter/Sort... and in the menu click on Clear Grid. (You must be viewing the filter grid for this command to be available). No more filter criteria are left, but the Sort choices are still there. Change any sorts to 'not sorted'.

There are more neat things to do with filters, but that's enough for now! Let's move on to another powerful part of a database - Queries.

Home > Jan's CompLit 101 > Working with Databases > Basics > Access Objects > Sort & Filter

Previous Next

Site Map
What's New

~~ 1 Cor. 10:31 ...whatever you do, do it all for the glory of God. ~~

Last updated: March 22, 2017