Query criteria help you zero in on specific items in an Access database. If an item matches all the criteria you enter, it appears in the query results.
To add criteria to an Access query, open the query in Design view and identify the fields columns you want to specify criteria for. If the field is not in the design grid, double-click the field to add it to the design grid and then enter the criterion in the Criteria row for that field. A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value.
If the value for that field in a given record is "Chicago" , Access includes the record in the query results. Here are some examples of commonly used criteria you can use as a starting point to create your criteria.
The examples are grouped by data types. Introduction to query criteria. Criteria for Text, Memo, and Hyperlink fields. Criteria for Number, Currency, and AutoNumber fields. Criteria for other fields. A criterion is similar to a formula — it is a string that may consist of field references, operators, and constants. Query criteria are also referred to as expressions in Access. It includes only those records where the Price or UnitsInStock field contains a value greater than 25 and less than Only records where the number of years between a person's birthdate and today's date is greater than 30 are included in the query result.
This criterion can be applied to any type of field to show records where the field value is null. As you can see, criteria can look very different from each other, depending on the data type of the field to which they apply and your specific requirements. Some criteria are simple, and use basic operators and constants. Others are complex, and use functions, special operators, and include field references.
This topic lists several commonly used criteria by data type. If the examples given in this topic do not address your specific needs, you might need to write your own criteria. To do that, you must first familiarize yourself with the full list of functions, operators, special characters, and the syntax for expressions referring to fields and literals.
Here, you will see where and how you add the criteria. To add a criteria to a query, you must open the query in Design view. You then identify the fields for which you want to specify criteria.
If the field is not already in the design grid, you add it by either dragging it from the query design window to the field grid, or by double-clicking the field Double-clicking the field automatically adds it to the next empty column in the field grid.
Finally, you type the criteria in the Criteria row. Criteria that you specify for different fields in the Criteria row are combined by using the AND operator. In other words, the criteria specified in the City and BirthDate fields are interpreted like this:. What if you want only one of these conditions to be met?
In other words, if you have alternate criteria, how do you enter them? If you have alternate criteria, or two sets of independent criteria where it is sufficient to satisfy one set, you use both the Criteria and the or rows in the design grid. Criteria specified in the Criteria and or rows are combined using the OR operator, as shown below:. If the criteria is temporary or changes often, you can filter the query result instead of frequently modifying the query criteria. A filter is a temporary criterion that changes the query result without altering the design of the query.
For more information about filters, see the article Apply a filter to view select records in an Access database. If the criteria fields don't change, but the values you are interested in do change frequently, you can create a parameter query. A parameter query prompts the user for field values, and then uses those values to create the query criteria. For more information about parameter queries, see the article Use parameters in queries and reports. The following examples are for the CountryRegion field in a query that is based on a table that stores contacts information.
The criterion is specified in the Criteria row of the field in the design grid. A criterion that you specify for a Hyperlink field is, by default, applied to the display text portion of the field value.
The syntax for this expression is as follows: HyperlinkPart [Table1]. Query result. For a list of such characters, see the article Access wildcard character reference. Returns records where the field is set to a blank but not null value. For example, records of sales made to another department might contain a blank value in the CountryRegion field. Returns records where there is either no value in the field, or the field is set to a blank value. Note: The characters? The following examples are for the UnitPrice field in a query that is based on a table that stores products information.
The criterion is specified in the Criteria row of the field in the query design grid. Use this crit erion. The following examples are for the OrderDate field in a query based on a table that stores Orders information. Returns records of transactions that took place on Feb 2, Remember to surround date values with the character so that Access can distinguish between date values and text strings.
You can also use the Between operator to filter for a range of values, including the end points. Returns records where the transactions took place on Feb 1, , March 1, , or April 1, Contain a date that falls in a specific quarter irrespective of year , such as the first quarter. Returns records of transactions that took place on the current day.
Returns records of transactions that took place the day before the current day. Returns records of transactions that took place the day after the current day. Returns records of transactions that took place during the current week.
A week starts on Sunday and ends on Saturday. Returns records of transactions that took place during the last week. Returns records of transactions that will take place next week. Returns records of transactions that took place during the last 7 days. Returns records for the current month. Returns records for the previous month.
A month's worth of sales records. Returns records for the current quarter. Returns records for the previous quarter. Returns records for the next quarter. Returns records for the current year. Returns records of transactions that took place during the previous year.
Returns records of transactions with next year's date. Returns records of transactions with dates that fall between Jan 1 of the current year and today. Tested for a Yes value. A value of 1 or -1 is converted to "True" in the Criteria row after you enter it.
Tested for a No value. A value of 0 is converted to "False" in the Criteria row after you enter it. Attachments In the Criteria row, type Is Null to include records that do not contain any attachments. Type Is Not Null to include records that contain attachments. Lookup fields There are two types of Lookup fields: those that look up values in an existing data source by using a foreign key , and those that are based on a list of values specified when the Lookup field is created.
Lookup fields that are based on a list of specified values are of the Text data type, and valid criteria are the same as for other text fields. The criteria you can use in a Lookup field based on values from an existing datasource depend on the data type of the foreign key, rather than the data type of the data being looked up.
For example, you may have a Lookup field that displays Employee Name, but uses a foreign key that is of the Number data type. If you do not know the data type of the foreign key, you can inspect the source table in Design view to determine the data types of the field. To do this:. The data type for each field is listed in the Data Type column of the table design grid.
Multivalued fields Data in a multivalued field are stored as rows in a hidden table that Access creates and populates to represent the field. In query Design view, this is represented in the Field List by using an expandable field. To use criteria for a multivalued field, you supply criteria for a single row of the hidden table.
Just below the name of the field, you will see a field representing a single value of the multivalued field. This field will have the same name as the multivalued field, with the string. Value appended. Drag the multivalued field and its single value field to separate columns in the design grid. If you want to see only the complete multivalue field in your results, clear the Show check box for the single value field.
Type your criteria in the Criteria row for the single value field, using criteria that is appropriate for whatever type of data the values represent.
DEFAULT
DEFAULT
Apply criteria to text values - Queries help you find and work with your data
You can supply alternate sets of criteria in a query by using additional criteria rows. If a record meets all the criteria for any criteria row, Access. Use criteria to get the most specific information from a query. (Get the basics from, introduction to queries.) In the example below, criteria added to the. A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value. For example.
DEFAULT
DEFAULT
Create a simple select query - Create a select query
You can use query criteria in Access to limit the results based on specific text values. For example the criterion, = "Chicago" shows all items that have. Use criteria to get the most specific information from a query. (Get the basics from, introduction to queries.) In the example below, criteria added to the.
DEFAULT
DEFAULT
DEFAULT
DEFAULT
5 comment