The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

Filtering Forms Go from Chaotic to Focused

Filters are magnificent things. They range from simple to extremely complex. This simple charcoal and glass wool fish tank filter pumps water through some glass wool and charcoal. As the water moves through the pump, the glass wool removes particles and the charcoal helps purify the water. This filter helps to supply fresh water for Bubbles the goldfish.

              
At the other end of the spectrum is the human liver. It cleans toxins from our blood. I am sure a physician or biologist could explain the process, but for me it is one of those magical things that just works. I marvel at the fact that it does its job so well. The magnificent work of the liver helps keep us on the green side of the lawn.

The benefits we get from filters up and down the complexity spectrum are immense. The filters in Microsoft Access are extremely useful as well. This month, we will focus on simple filters that get the job done quickly and easily.

 

Implementing Quick Filters

You have probably used filters in your queries. It is what you put in the criteria section when you are in the query by design window. Access takes what you typed there and turns it into a filter. When you are working in a form or in a table, there are some nifty tools that you can create with very little work – my kind of tool.

Take a look at the form below. It is a tool that I use to keep track of Access Wizard articles, both past and planned. If I want to focus on a single month, it is quite simple.



On the Ribbon at the top of the screen (see below), the second group on the Home tab is Sort & Filter. In that section, you will see a funnel with a lightning bolt next to it labeled Selection. When you click on that tool, it gives you four options, all of which are very simple filters that will either get you an exact match, non-matches, or ‘contains’ options. The filters that appear are consistent with the text box you are on when you click this Filter Selection button.



Another way to do a very fast filter is to right-click in any cell of your form. When you right-click, you get the pop-up filter below. Note that it also starts with whatever value you are on and gives you the same types of selections.



One thing to note in the options is that it translates dates into text. It does this because I had selected a combo box. On the form, it sees the contents as date; however behind the scenes is something else: it is looking at the underlying key within the combo box and will filter on that. Unless you are doing complicated development, you can ignore this nicety since when it applies the filter it will give you what you expect.

If you were sitting on a true date field or a numeric field, you would then have the option of equal to, less than, etc.
 

Conclusion

In this month Wizard, I have introduced you to some very simple filters that are available with the mere click of your mouse. These filters are not terrifically powerful, but they are very simple to use.

If you are interested in more powerful filtering techniques, stay tuned. I will be addressing more robust filtering techniques in future Wizards.

Tip of the Month: The Access Wizard Archives


I have been writing the Access Wizard since 2004. In these Wizards, I have covered a wide range of topics. Some are simple, like this month’s filters; others are geared toward developers such as the August 2017 Wizard, which showed a technique to call code in one Access application from another Access application.
.
Whether you are a beginning or advanced Access user you might want to take a look at the library available at my website archive: http://custom-software.biz/accessWizard.html

When you are on the archive page, press the control F button to get a search box. From there you can type in the subject that you are interested in learning more about. Strange though it may seem, in the past when I have used Google in an attempt to solve a tricky problem, I have had Google point me to the Access Wizard for the solution.

As I mentioned, I have been doing this for a while. Some of the topics that I have written about that are sufficiently arcane (for example see the December 2014 Wizard) that I had forgotten I had written about them.

In any case, the Wizard library is there as a resource to make you a more capable Access user or developer.


Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886