When working with calculation or database programs it is very important to have tools that allow you find specific data at certain times, especially on those occasions when working with a Lots of information where some searches specific fields it gets more complicated.
Fortunately, users of Microsoft Access have a function known as Filters, and which has as objective to allow information to be filtered so that this can be easily found. This means that this tool will allow to be displayed in the worksheet only those data that are really interesting and with which you want to work.
As it is considered a very important tool in software, it is essential that everyone users can apply it when needed. That is why here we are going to teach you a little more about what they are filters, what they are for and how you can start applying them in your databaseTo do this, follow in detail everything that we will explain in the post.
What is a filter in an Access database and what is it for?
Filters or filter system as it is also known, it is a very useful tool since it allows see only the data you want to show, that is, those that the user really needs. This function is used mainly for those occasions where you want show specific records within a form, query, report or datasheet. These data can be used to work solely with them or to print them.
Therefore, this filter element It will allow you to decide what data you want to appear in a view without modifying the design of the underlying object. It is very useful mainly for those occasions where you work with very long tables, where you only need to work with some specific data from there.
An example of this would be a customer table of a automobile company, where there are currently registered more than 500 people, but this time you only want to work with those people you have Toyota brand cars. For these cases the user can use the filtering system and in that way the view only those customers who own a Toyota car, so everyone else data will disappear unaffected.
What is the usefulness of filters in databases? Advantages of using them
Without a doubt, being able to make use of this type of tool in these programs will offer you a many advantages when using them, and it is that when they are applied a view is obtained that only contains the records that include only the values that have been previously specified, while all other data will be hidden until finished work with the filter.
In the case of Access can be found different types of filters, where each of them can be easily apply and remove. In addition, it should be mentioned that the use of each of the types of filters it will depend mainly on the values that are being used in the fields. Here you will have the opportunity to select if the search is going to be "Is equal to", "Not equal to", "Less than", "Greater than", "Between", this will allow you to specify the filtering method.
However, it is important to mention that these options will not always be the same, since this will vary according to the data type in the selected field. If you select a number field, the options that appear there vary as you can see in the following picture.
And so on it will vary according to the field that is selected. Considering all this, it is important to mention what are the main advantages that can be obtained at the time of application.
To do this, follow each of the benefits that we will show you below:
- It allows decrease the amount of data displayed on the screen.
- The user will only have in view the data that interests them and with whom you want to work.
- All other data that is not being used or appears in the filter system they were hidden until it is erased.
- Facilitates searching for specific fields in very long documents.
- It helps you to decrease working time, for the that your performance it will be much greater.
- When using filter tool the user has the opportunity to specify what values he wants to have in the view, that is, those who want visualize.
Learn step by step how to use filters in Microsoft Access databases
As already mentioned above in Access can be applied different types of filters, each of them will help you find specific fields in a very simple way. However, it is not all about knowing how to apply it, it is also important to know how they can be removed, changed or saved.
That is why here we are going to teach you how to perform each of these procedures, to do this, follow each of the methods that we will teach you below:
Select and apply filter type
At the moment of wanting apply a filter on the database you will have the opportunity to select between different types of filters, which will be used depending on the type of data in the fields. In addition, it should be mentioned that you can also filter a form or one datasheet by filling out a form.
Currently Microsoft Access has four filtering systems available, which we will show you below:
- Common filters: They allow you to filter specific values or a range of values.
- Filter by selection: It is used to filter all the rows of a table that contain a specific value that match the value that has been selected.
- Filter by form: It allows filtering in several fields of a form or data sheet, you can also use it to search for a specific record.
- Advanced filter: Finally, there is the advanced filter where the custom filter criteria are defined.
Common filters
It should be mentioned that all the database fields offer common filters except OLE Object fields and the fields that show calculated values. Also, the list of available filters it will depend on the type of data being used and of the values of the selected fields. In order to apply this filter you need to do right click mouse on the field you want to filter.
If what you are looking for is filter by multiple columns or controls, then it is necessary that you should select and filter each column or control separately, or in that case use the advanced filter option. If what you want is see available filters for a specific field as it can be the dates of manufacture of the cars, then you need to follow this route: "Home"> "Sort and filter"> "Filter".
You can also perform different ways to filter data such as the following:
- To filter specific values you can make use of the checkbox list, the list includes all the values currently displayed in the field.
- Now if what you want is filter on a range of values, then you need to click on one of these filters and there you specify the necessary values. In this case you can select intervals of the cars that were manufactured from 2015 to 2022 and press the key "Enter" in this way you will only have the records that are between the interval of those 5 years.
Also keep in mind that the values of a field that contains date can reach affect the list of specific filters of a type. In the event that one of the most recent date values of a date field belongs to the last two years, then a l will appearmuch longer and more detailed filter view, the dates they have more than two years old they have less filter options.
In accordance with all this, here we are going to teach you the main steps you must follow to apply a common filter in your database, to do this, follow each of the instructions that we will teach you below:
- The first will be open in a data table some of the following views: Datasheet, report, form or presentation.
- The next thing to do is to verify that no filter in sight. In the record selector bar you can check this, there you can see if it hasn't been leaked yet.
- Now you must click on any other part of the worksheet and on the "Start" you must go to the section "Sort and filter" and you click "Filter".
- So you can apply a common filter you must select the option "Text filter" and then select the filter you want, in the case of the "Equal to" and "Between", you must specify the corresponding values.
In the case of having to apply a filter based on field values, it is necessary to uncheck the check boxes next to the values by which you do not want to filter and then click on "To accept". To be able to filter by a value or few values, you must deactivate the checkbox "Select all" this will prevent all the data in the table from being selected so you can select it manually.
If what you are looking for is to filter by null values, that is, in the absence of data in text fields, number or dates, then in check box you must disable the option "Select all" and then you must activate the check box "Empty".
Filter by selection
This will allow you to see all the rows in a table that have a value that matches the value of another row, in this case you can quickly filter the Datasheet view choosing a specific data and selecting the command Selection. There you will see a drop-down menu with various filtering options available.
The options that will appear there will vary depending on to the data type of the selected value. It is also possible to get an access to the select filter options by doing right click on the corresponding field. In this case you can select any of the date boxes and then go to the tab "Start" and there in the section "Sort and filter" select the category of "Selection" in this way they will be shown to you the available commands.
Therefore, having all this completely clear, the following will teach you each of the steps that you will need to perform to apply a selection filter.
To do this, follow in detail each of the steps that we will explain below:
- The first thing will be to open a query, form, or table, either in a view of "Form, data sheet, report or presentation."
- Again you must make sure that no filter is applied to the data you want to work with.
- Now go to register with the value you want to use as part of the filter, for this you must click inside the spine where the value is found.
- To be able to filter the selection, you must follow this route already mentioned: "Home"> "Sort and filter"> "Selection" and then select the filter you want to apply.
Filter by form
Considered as one of the most useful filters when what you are looking for is to filter several fields in a form or in such case that the user needs to get a specific record in worksheet. For this the Access system takes care of create a form identical to the original, in this way the user can fill it in with the fields they want, once this is completed Access will search the records that contains each of the values that are specified.
This type of filter is recommended to be used in cases such as the following, when handle customer data and you want to get alone clients residing in Madrid, allowing to obtain only those records that are necessary and with which you want to work.
Therefore, to apply this filter you must follow these steps:
- To start you must start a query or table in Datasheet view.
- Next you must make sure that there is no filter applied on the view. You can verify this through the registry selector bar.
- From the tab "Start" and in the section "Sort and filter" you must click "Advanced" and then in "Filter by form".
- The next step will depend on what view you are using if it is "Data sheet" then you must click in the first row of the column you want to filter by, there you should select arrow what appears and then you choose a value. To add more values to filtering you must click the O tab located at the bottom of the datasheet and there we add a new value.
- If you are using the "Form view", then you need to select the arrow that appears on the control and then the value you want filter, So you can add new values select the tab O located at the bottom of the form.
- If what you are wanting is to specify two sets of alternative criteria, this in case you want to specify contact names or clients that they live in Madrid and that you have a mark of Renault car, then you must write the set of criteria In order for you to specify them, you must click on the O tab and then you select the set of criteria. For a field value to work as a filter independently of the other values of the other fields, you have to specify this in the section "Search for" and in each tab O, is token or represent an alternative set of filter values. In this way you can start applying bliss function in Access.
Advanced filter
And they finally meet advanced filters, these are applied occasionally, in these cases the user must personally write the criteria that he wants to be applied in the form. In these cases, the person can specify that only those customers who have made purchases in last 5 days of month. When applying this filtering system it is necessary to write expressions very similar to Excel formulas and criteria specified when creating a query.
Therefore, to apply this tool you must follow these steps:
- As in all the previous steps, you must open a query or form in any of the following views: Report, presentation, form or Datasheet.
- You should also make sure that it has not been applied no filter on selected view.
- Now you must go to the tab "Start" and then in the section of "Sort and filter" and then you choose the option of "Advanced". There you must click on "Clear all filters."
- Within the group of "Sort and filter" again you must choose the option of "Advanced" and then you select the command "Filter or advanced order" in the drop-down menu.
- Now you must add to Grid The fields you want to filter appear.
- In the option of "Criteria" For each of the fields you must specify a criteria, these are applied as a set and only the records that match all the criteria in the row of "Criteria".
- If you want to specify some alternative criteria for a special field, then bes write the criteria first in the Criteria row and then in the O row.
Remove a filter
You may have already applied one of the filters explained above in your database but for some reason you don't want it anymore or just you applied the wrong filter or incorrectly. In this case it is possible to remove them in a very simple way.
For this you can change the view without filtering the data, to remove them you must click on the option "Filtered out" in the registry explorer bar to return to full view. When the filters have been removed, they will be removed temporarily filters all fields in view. In case you want to go back to apply the filters more recent then you must click on "Without filter" in the browser bar.
Reapply Filter
If you have removed the filter system by mistake or have you realized that if you need apply this tool, then you just have to go back to select the corresponding fields to apply them again. In this case you must follow the steps mentioned above in the post according to the type of filtering you want to apply in the form.
Clear filter
If you've been working on your database and previously you applied a filter but you have realized that you no longer need it, then you can remove it. At the time of clear the filter it is automatically removed from the view so it cannot be applied again. In this case the user can delete either a single filter of a single field either clear all filters from all form fields.
To carry out this procedure, you need to follow these steps:
- For remove a single filter of a single field you must click the right button on the filtered column and then in the option of "Remove filter from field name."
- So you can clear all filters from all fields you must go to the tab "Start" and in the section "Sort and filter" you are going to select the option "Advanced", after select "Clear all filters" in the context menu.
Save filter
And finally you can save filters to use them on other occasions, which will become very useful especially at the time of want to save time and work faster. And is that when filters are applied on a table or form and this closes this is saved from automatic way together with the object, so they are available so that they can be applied again.
But, it should also be mentioned that The filters they are not going to automatically apply next time you work in the database, so it is necessary that you make sure that active filters they are applied automatically the next time a table is started.
To do this you must set a property value "FilerOnload" itself within the object sheet. In this way, the property FilerOnLoad can be applied the next time the object is opened, when changing its configuration you will have to close the object and reopen it to apply the new configuration.