How to Use Query Criteria in MS Access Effectively

This article explains the importance and use of Query Criteria in MS Access while also explaining a proper method to use it.

Query Criteria is used for retrieving specific items from an Access file. Users use Query criteria for retrieving search and view data, without performing an in-depth search of the database. If an item or information matches with all criteria entered by the user, it automatically appears in query results. This helps in saving time and energy of the user. Users use Query Criteria when they want to limit the results of their query based on particular values in the database fields.How To Use Query Criteria In MS Access

Feature and Functions of Query Criteria

  • Query Criteria is an Access Expression which compares query field values in order to determine, whether a record containing each value should be included or not.
  • Query criteria allow users to zero in on a specific piece of information from their Access Databases.
  • For a more specific Query search, users should use multiple criteria in their query. Users can add multiple fields in their query criteria to make their query more specified towards a particular database section.
  • Some criteria are complex while some are simple, and operate through basic constants and operators. The complex criteria might require users to use special operators, functions or they might even require them to insert field references.
  • In order to add criteria in a query, the user needs to open it using the Design View. Using the Design View, users can easily add as many fields in his/her criteria without any hassle or confusion.
  • Users can identify their specific criteria, by viewing or analyzing the query result.

Steps to Use Query Criteria in MS Access

  • Use Query CriteriaStep 1: Open the Access Database file from which you want to retrieve the data. Then go to ‘Create’ tab and select the Query Design.
  • Step 2: Now go to the Table dialog and select the table for which you want to retrieve the data.
  • Step 3: Now the user needs to create a query by adding some fields in the grip section from their database. For instance, if a user is accessing a CustomerData table, they can add Country, FirstName, LastName, and Email in the grid.
  • Step 4: Once the query is created, the user needs to run it, which will automatically generate the query result.

In case the users need to search for a specific category, they can search for it, while adding their search target in the Query Criteria. For Instance, if a user wants to search for customers, who live in ‘France’, ‘UK’ or ‘USA’, they can do so, by adding these countries in Customers’ Criteria Row.  This will only retrieve the data of the criteria mentioned above.

Users can also search for multiple fields by adding it in their Criteria.

Query Criteria is a simple way which allows users to access their data, without running an internal search. Query Criteria generates results for specific information. A query is like a filter which accesses data specified by users, based on his/her criteria. This means that the whole functionality of Query Criteria depends on how a user defines his/her criteria for the fields that he wants to search.

While MS Access is rich in features, it does at times falls short in robustness. If you are using MS Access for business it is prudent for you to invest in an Access recovery application.

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and excel recovery software products. For more information visit www.datanumen.com

2 responses to “How to Use Query Criteria in MS Access Effectively”

  1. I find sometimes OR queries can run very slow. Given the opportunity, I find a join to a table containing a list of the OR criteria runs faster. It also allows you to change the criteria on the fly. Failing that I have also run multiple queries, one for each of the values, and dumped the results into a result table. I usually do this by looping through the values and replacing the condition in the query in VBA.

Leave a Reply

Your email address will not be published. Required fields are marked *