Access: The most important filters for forms
In forms, you have various options for filtering your data in Access without having to create queries. You can find out which are the most important filters in this practical tip.
Access: Use general filters for forms
The general filter allows you to select values. Text filters such as "Contains" or "Equal" are also possible with this type of filter. You can use the procedures described here as of Access 2010.
- You can only apply general filters to one field. If you want to filter multiple fields, you must apply the filters separately or use an advanced filter option.
- Navigate to the field you want to filter.
- Now click on "Filter" in the ribbon in the "Start" tab.
- If you have a form in the table view, you can also click the arrow in the field heading on the right edge of the column.
- The possible filter options are now displayed.
- You could now sort the field contents from A to Z in ascending or descending order.
- You will also see a list of all the values that are saved in the current field.
- You can use the check boxes to filter for one or more values. Remove the check marks if you want to exclude the content from the result.
- Select "Text Filter" to get detailed results. For example, if you want to filter names from a form that contain the letter T, select "Contains" and then enter a T.
- Sometimes you won't see the general filter. This is because this option is not available for fields of the types "Yes / No", "OLE object", "Memo" and "Attachment".
Access: Use selection-based filters
If you want to set a filter for a specific selected field content, you can do this with the selection-based filter.
- Navigate to "Start" in the ribbon and click on "Selection" in the "Sort and Filter" group.
- Now you get a dropdown list which shows you the available filter options for your selection.
- For example, you have a student list and you have selected a field with the entry "Student". The selection-based filter now gives you various filter options around the term "student". Filter "Contains students" you get in the result of all students in your class. The students are excluded from the result.
- The options for selection-based filters also differ depending on the data type.
Access: Use form-based filters
This filter helps you if you want to filter several fields on one form.
- On the Home tab, in the Sort & Filter group, click Advanced> Form-Based Filter.
- Access now creates an empty form with your fields.
- You can fill in as many fields as you like with filter criteria.
- Access then shows you matching records.
- You cannot use the following field types with this filter: "Multi-value", "Memo", "Hyperlink", "Yes / No" and "OLE object".
Access: Create your own criteria with the special filter
Sometimes the standard filters are not enough. With the special filter you can write your filter criteria yourself. In order to use this filter correctly, you should already be familiar with creating queries.
- Open your form in the form view.
- Make sure that no data has been filtered yet.
- To do this, click on "Advanced" and "Clear all filters" in the "Sort and Filter" group on the "Start" tab.
- If "Clear all filters" is hidden, no filters are active and you can proceed to the next step.
- Click "Advanced" again and then "Special Filters / Sorting".
- Add the fields from the list that you want to apply a filter to.
- Enter the desired criteria in the "Criteria" line of the fields.
- The criteria are grouped. You will only be shown the data records that meet all criteria of all fields.
- If you need alternative criteria for a single field, enter the first criterion in the "Criteria" line and the next criterion in the "Or" line.
- All criteria that you use in the "Or" line are filtered as an alternative to the criterion in the "Criterion" line.
- For example, if you enter "pupil" as the criterion, you will only receive results that have this entry. If you enter "pupil" under "Or", you will receive data that includes both pupil and pupil.