'Serving the Microsoft® Access
Filtering in MS Access
Filtering is the method which allows you to view the table records in a different way. This is usually done by viewing the specific records that meet some specific criteria. For example, you may wish to see all the records in your ACCESS 2000 table that have an age field value greater than 18. If you have created a company database which stores the information of your company employees, you may need filtering of data, occasionally. For instance, you may want to see the list of employees whose salary is greater than 5000 and so on.
Thus, filtering is a very useful method in which you can view, save, and print only the desired and required information from your huge database. Microsoft Access provides you several easy ways to perform the filtering. The process is really fast.
Often, I will create a query for these tasks, but there are times when I use filters instead.
Filter by Selection in Access
Access provides the facility to filter the records containing similar values of data for a specific field. For example, you may want filter the records that have value "Peter" in the "NAMES" field.
In order to filter the records by selection in an Access table, open the table where you want the filtering of data. Just place the cursor on the specific value which you want to use for filtering criteria. For example, if you want to see all the records which have value "18" in their AGE field, then just point your cursor on one of the "18" values for the "AGE" field.
You should see a button on the tool bar saying "FILTER BY SELECTION". It looks like a small funnel with a yellow lightning bolt under it. Just click this button and you will be presented with all the records in the table which have value 18 for the AGE field.
(NOTE: this article was written based on research using Access 2000. It should work on other versions, although the look of some of the buttons mentioned may change in newer versions.)
NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.
Filter by Form in Access
In some cases, the Filter by selection method may not be very helpful as you may need extra efforts to find your desired filter value to start with. In this situation, you may find that the "Filter by Form" method is better.
The "Filter by Form" method creates a blank table for the selected table. This blank table contains all the fields of the table with a drop down box for each field. This drop down box contains all the unique values of records for the particular field. In this way you can very easily select the field value for which you want to filter the table records. If you want to filter the records by the exact field value then simply select the data value from the drop down list and click the "APPLY FILTER" button on the tool bar. That button looks like a small funnel. If you want to enter some other criteria then you can type that in the criteria into the field.
For example if you want to filter all the records that start with "Mr" in the Name filed, then you can type this filter in the Name field: "Mr*".
Similarly if you want to see all the records in which the name begins with letters A to H then you should type this filter criteria in the Name field: "<='H'".
(article continues after sponsor spot)
Saving a Filter
If you want to filter the records on the basis of the same criteria again and again, then it is advisable to save the filter. You can save the filter as a query by selecting the "Save As Query" from the main menu and give a name to the filter to save.
Using VBA to Filter the Results in a Report
If you have a report and want to filter the report without hard-coding the settings (parameters) in the Query Builder, then you can do that by using the OpenReport command in VBA.
Create a button on your form, "cmdFilterTest", with the caption "Show Names Starting with S" and add the following simple VBA code behind this button using the "On Click" event.
Private Sub cmdFilterTest_Click() DoCmd.OpenReport "rptTestFilter", acViewPreview, , "Name like 'S*'" End Sub
On clicking the button "Show Names Starting with S" the above code "cmdFilterTest_Click()" is called. This code will cause the report to be created, but only with records that match the filter criteria.