A Guide to Using Criteria Correctly in Microsoft Access Queries

Adding Criteria to an Access Query Focuses on Specific Information

Businessman examining cube at desk in office
Tom Merton / Getty Images

Criteria target certain data in Microsoft Access database queries. By adding criteria to a query, the user can focus on information that has key text, dates, region or wildcards to cover a wide range of data. Criteria provide a definition for the data pulled during a query. When a query is executed, all data that does not include the defined criteria are excluded from the results. This makes it easier to run reports on customers in certain regions, states, zip codes or countries.

 

Types of Criteria

Criteria types make it easier to determine what kind of query to run. They include:

  • Numeric—Queries based on a number, such as a date, dollar amount or an area code
  • Text—Queries based on text, such as country, last name or information from a comment field.
  • Null—Queries that look for entries that have a specific field left blank, which is useful for making sure all pertinent information is provided in a database, such as for inventory or maintaining customer information.
  • Wildcard—This can be any of the previous types of queries. A wildcard pulls a wider range of data and is best used when a user is uncertain of the specific date or needs to pull a range of data about all customers who ordered a certain type of service.
  • Conditional—Used to pull data based on multiple types of criteria. Conditional criteria are advanced, and users should become accustomed to basic criteria before trying to use conditional criteria.

    How to Add Criteria in Access

    Before getting started on adding criteria, make sure you understand how to create queries and how to modify a query. After you understand those basics, the following walks you through adding criteria to a new query.

    1. Create a new query.
    2. Click on Criteria for the row in the design grid where you want to add the criteria. For now, just add criteria for one field.
    1. Click Enter when you are finished adding the criteria.
    2. Execute the query.

    Examine the results and make sure the query returned data as you expected. For simple queries, even narrowing down the data based on criteria may not eliminate a lot of unnecessary data. Becoming familiar with adding different types of criteria makes it easier to understand how the criteria affect results.

    Criteria Examples

    Numeric and text criteria are probably the most common, so the two examples focus on date and location criteria.

    To search for all purchases made on January 1, 2015, enter the following information in the Query Designer View:

    • Field – enter Date
    • Table – enter Purchase
    • Criteria – enter 1/1/15

    To search for purchases in  Hawaii, enter the following information in the Query Designer View.

    • Field – enter Country/Region
    • Table – enter Clients
    • Criteria – enter Hawaii

    How to Use Wildcards

    Wildcards give users the power to search more than a single date or location.  In Microsoft Access, the asterisk (*) is the wildcard character. To search for all purchases made in 2014, enter the following.

    • Field – enter Date
    • Table – enter Purchase
    • Criteria – enter Like “*14”

    To search for clients in states that begin with “W,” enter the following.

    • Field – enter Country/Region
    • Table – enter Clients
    • Criteria – enter Like “W*”

    Searching for Null and Zero Values

    Searching for all entries for a particular field that is empty is relatively simple and applies to both numeric and text queries.

    To search for all customers who do not have address information, enter the following.

    • Field – enter Address
    • Table – enter Clients
    • Criteria – enter “”

    It may take a while to get accustomed to all the possibilities, but with a bit of experimentation, it is easy to see how criteria can target specific data. Generating reports and running analyses is considerably simpler with the addition of the right criteria.

    Considerations for Adding Criteria to Access Queries

    For the best results, users need to think about what needs to be included in the data pulls. For example:

    • What kind of results are needed? Queries can be run on numeric values, dates, text and null values.
    • What is the simplest way to pull the most detailed data? Users who are only interested in deliveries made over the course of a year and who their best customers were for the same time only need a query that pulls data and includes customer names and dollar amounts for the orders. Users who want to pull data on new contracts based on the latest marketing campaigns need something more complex; the query needs to consider several different fields.
    • What is the least amount of data needed? This determines how much should be included in the query, which narrows the necessary criteria.
    • Is there an existing query that can be modified? When first starting with Access, the answer is no, but it is best to start by looking over existing queries as the database becomes more robust.
    • Is it likely that this query will need to be pulled again? This helps determine whether the query should be saved and stored for all users who may need it. Even if a company is only pulling data for 2015, wildcards can be used in the saved version so that the query can be recycled.
    Format
    mla apa chicago
    Your Citation
    Chapple, Mike. "A Guide to Using Criteria Correctly in Microsoft Access Queries." ThoughtCo, Oct. 17, 2017, thoughtco.com/using-criteria-in-microsoft-access-queries-1019921. Chapple, Mike. (2017, October 17). A Guide to Using Criteria Correctly in Microsoft Access Queries. Retrieved from https://www.thoughtco.com/using-criteria-in-microsoft-access-queries-1019921 Chapple, Mike. "A Guide to Using Criteria Correctly in Microsoft Access Queries." ThoughtCo. https://www.thoughtco.com/using-criteria-in-microsoft-access-queries-1019921 (accessed May 25, 2018).