You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
One of the key things databases provide is the ability to sort, filter, and group data. Of course Access provides this today but Access 2007 makes it quicker and easier to sort, filter, and group data in forms, reports, and tables. Today we'll look at just filtering and basic sorting in a form datasheet - in this case we'll use the Issue List form from the Issue tracking application. In addition we're only looking at the top-level UI for sort and filter. Next post I'll go through the new grouping and sorting functionality, and we'll take a look at that in reports. There's a ton of functionality here, and substantial changes to the UI, so it'll take a couple of posts to get through it.
We're using the Issue List form from the Issues tracking application. This is a split form, with a datasheet below and some buttons above providing navigation and other functionality. You can see the form below:
(Click image to enlarge)
You'll notice that next to column name for each column, there's a small arrow pointing down. Clicking this arrow opens the simple sort / filter menu:
In this dialog, you can sort by simply clicking the Ascending or Descending buttons. Filtering for text strings is as simple as clicking the appropriate boxes at the bottom of the form. This menu is sort of a combination menu / dialog. If you click the Sort buttons it dismisses immediately like a menu, but if you start checking the checkboxes it behaves like a menu and you click the OK / Cancel buttons. It makes more sense to use than to talk about!
Above the checkboxes, there is an item for "Text Filters". Clicking this brings up functionality to set text-aware filters, with the ability to choose from a number of conditions. The choices are:
Choosing a condition brings up the following dialog with the appropriate condition. This is the example for "Equals":
Since Access understands dates as a data type, we can provide date-aware filtering, so if the user has selected a date column, we can provide appropriate filters for dates:
Of course the same sorting and filtering functionality is available directly on the ribbon:
As is filtering:
Clicking the Filter button brings up the same filter menu shown above.
All of the filtering commands above take the column as the selection and understand the data in the column as a whole. If the user has a row selection in a column, we can provide special filters for that row. For example, in the screenshot below the user has right-clicked on "Cliff Jones" and in addition to the regular text filters, she can select to filter for Cliff with one click.
If the user has a selection in a row, the same filtering is available from the ribbon as well as the right-click menu.
All of Access's existing rich filtering functionality is still available on the Advanced filtering drop-down.
Users can filter by form, setting up the form with one click:
The user then selects filter criteria in the form, and applies the filter by clicking the "Toggle Filter" button on the ribbon:
And the results are displayed in the original form:
Notice in the image above that the "Assigned To" and "Priority" columns have filter icons next to them, to indicate that they have an applied filter.
To remove the filter, the user simply clicks on Toggle Filter on the ribbon again:
And the existing Query By Example filter UI is still available as well. The user simply selects "Advanced Filter/Sort..." from the dropdown and is taken to the query builder to begin constructing a rich query.
Next post, I'll go into the new Sorting and Grouping UI for reports, and we'll take a quick look at how the Filtering functionality above transports to reports.
This stuff looks really great! In Access 2002, sorting on a combo box field sorted on the underlying key, rather than the combo's displayed text. How does sorting and filtering work in the new version? Does it use the combo's key or the first displayed column? (I sure hope it's the latter) Also, does all this work the same way in ADP's
Is Filter By Form now available in Runtime? Thanks,
Yes, we got the combo box sorting to work right in 12. Actually this isn't just combo boxes, but any lookup, and in 12 we sort on the display value (e.g. the name) rather than the actual value (e.g. the ID). All of this should work just the same in ADP's. On the runtime, we're still working out exactly what will be available and what won't, so I can't say for sure what will be available.
Eric, You made my day!
When a condition of filter is apply to the the existing applied filter, It do logical AND with previous one. How about remove filter ? Can Access remove each condition of filter reversely step by step ? I think this feature will enhance using of filtering in Access. Thanks,
The filter actions are hooked up to the undo stack. so you can undo filters reversely step by step with the undo command. Also, in accdbs and mdbs you can clear filters from each column. This command will be available programmatically in post beta 2 builds via runcommand.ClearFilterFromField. In ADPs you only have the option to remove the entire filter. One thing, if you use the above filters in a query and then choose Save As | Query the filter is flattened into the where clause. This makes it much easier to build fairly complicated where clauses in queries without having to use the query designer.
Re: the sorting of lookups and using the displayed column. Will this be an option? I can see it being very useful but in the databases I build users rely more on state number for sorting than name and I'd hate to change the way things work now.
I would like to reiterate the request for Filter By Form being available in the Access Runtime. I could never understand why this was excluded, and its a really nice feature that I would promote with my end users if it were available.
I noticed that your reply on the sorting and filtering on the displayed column only mentioned sorting. How are you handling the odd text fields for filtering? By odd I am referring to text that has single quotes, dbl quotes, hash etc? Is there a new delimiter we can use on text fields to eliminate the various Replace functions we have all written to try and escape the all the escape chars? Thanks,
I'm sorry for the OT, but it is possible with Access 12 to insert SubForms on continuous forms?
For sorting by ID rather than display value, you could create an advanced filter. I'm still digging on this (Clint, the PM that owned the feature is out taking care of a new baby) so more on this when he gets back. On the filtering question, I'm not sure I understand the question, but if it is what I think it is, you shouldn't have to deal with the funky characters anymore. The example I'm looking at is filtering for a value of My"String". That just works in 12 and failed in 11. Access 12 still doesn't support sub forms in continuous forms. You can imagine a nice nested table structure, but we didn't get there this time.
Congratulations to Clint !*!*!*!*! My"String" is a fairly good example ... how about ... Steve's #1 Code "Emporium" and what would I use for a delimter for this string if I wanted to use a form's recordset? Steve
I am having trouble using Filter by Form when I want to search using two criteria. For example, I want to search for Criteria1 AND Criteria 2. When I do the search, Access ignores my second criteria. What is unusual, is that if I go into the Properties list and read the informaiton beside Filter it will say: ((form_name.criteria=-1) AND (form_name.criteria=-1)). It seems that it never recognized the second criteria.