Version 1.1           Approx 1.3 MB                 First Published 30 Dec 2023


Access provides many different ways of filtering data to find specific records. To facilitate this, some common filters are provided that are built into every view.
Filters can be applied or removed without altering the underlying data.

Methods of filtering include the use of:
a)   select queries with TOP, DISTINCT, WHERE or HAVING clauses.
b)   using filter on load in the property sheet
c)   building SQL strings to limit data using code - see Multiple Group & Filter
d)   right click context menus in datasheets, continuous forms and split forms
e)   multiselect checkbox filters in datasheets and continuous forms - see Multiselect Filter in Continuous Forms

For details of the various methods, see the Microsoft Help article: Apply a filter to view select records in an Access database



Filter By Form

This article will discuss the relatively little used Filter By Form feature.
Filter by Form is a feature in Access that allows you to filter on several fields or find a specific record.
Despite the name, the feature is in fact just a different way of building a query to filter data. It can be used on datasheet tables and queries as well as in forms.

To use it, you need to click the Advanced Filter Options button in the Home ribbon and select Filter by Form.


Access creates a blank form or datasheet that is similar to the original one:


You can filter on as many fields as you wish. For example:


Doing this creates a temporary query. Click Apply Filter / Sort in the ribbon or the right click context menu to display the filtered output.


You can also use the Advanced Filter / Sort option to achieve the same results.
In this case, the temporary query filters are displayed in query design view where further changes can be made:


In either case, the filters will be saved for the next time the feature is used.
To save the filters permanently, click Save As Query on the context menu or clear Clear Grid to remove the filters.

The Filter By Form window includes an OR tab to allow further modifications to the filters:


The results include both sets of filters used.


Click Delete Tab to remove the filter criteria form any one of the filter by form tabs.

You can add different filter criteria to multiple OR tabs but can only view one tab at a time. This may make it difficult to keep track with the filters used.

However, in the Advanced Filter / Sort view, the two sets of criteria are shown on separate lines as for a standard query.


Both methods are particularly useful if you want to try out various filters without saving them permanently.
These also provide different methods of building relatively complex queries for those who prefer not to use query SQL or code.



Issues and Limitations

As already mentioned, the Filter By Form approach works for any type of form as well as datasheet tables and queries.

However, it does have some significant limitations that you need to be aware of. For example:

1.   It can only be used to filter the main form in a main/subform arrangement

2.   It cannot be used with unbound forms. In such cases, the ribbon menu remains disabled:


3.   The total number of records in the underlying table should not exceed the list limit in Access options (default limit = 1000):


      If the number of records does exceed this limit, the only options available using Filter by Form are: Is Null / Is Not Null


      This is the case even if the table data is first filtered using a standard query

      To work around this issue use a different method of filtering e.g. Advanced Filter / Sort.
      Alternatively, you can increase the list limit is Access Options but this will affect performance if the limit is very large.

4.   Not all query types can be displayed and experienced developers may therefore find little benefit in using this approach.



Download

Click to download the example database:   FilterByForm     ACCDB - approx 1.3 MB (zipped)



Video

In the near future, I intend to create a YouTube video to demonstrate both of these approaches. A link will be added to this article.




Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 30 Dec 2023

Return to Example Databases Page

Return to Top