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. This is useful if you want the form to be more restrictive than the table.
The form rule is applied, and then the table rule is applied. If the table is more restrictive than the form, the rule defined for the table field takes precedence. If the rules are mutually exclusive, they prevent you from entering any data at all. The date field now requires values earlier than the year , but the form control requires dates have that year or later, thus preventing you from entering any data at all. Right-click the control that you want to change, and then click Properties to open the property sheet for the control.
Click the All tab, and then enter your validation rule in the Validation Rule property box. Tip: Click the Build button to start the Expression Builder.
Validation rules use Access expression syntax. For more information about expressions, see the article Introduction to expressions. Enter a value with a percent sign. For use with a field that stores number values as percentages. Tests for values equal to existing members in a list. Comparison value must be a comma-separated list enclosed in parentheses. Tests for a range of values. You must use two comparison values — low and high — and you must separate those values with the AND separator.
Forces users to enter values in the field. This is the same as setting the Required field property to Yes. However, when you enable the Required property and a user fails to enter a value, Access displays a somewhat unfriendly error message. Note: You can also use AND to combine validation rules. You can use wildcard characters in your validation rules. Each of those standards uses a different set of wildcard characters.
In the Access Options dialog box, click Object Designers. Creating tables. Restrict data input by using validation rules.
Need more help? Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue. Clear instructions. Easy to follow. No jargon. Pictures helped.
Didn't match my screen. Incorrect instructions. Each SQL clause is composed of terms — comparable to parts of speech. The following table lists types of SQL terms. A combination of identifiers, operators, constants, and functions that evaluates to a single value. Access ignores line breaks in a SQL statement. However, consider using a line for each clause to help improve the readability of your SQL statements for yourself and others.
The semi-colon can appear at the end of the last clause or on a line by itself at the end of the SQL statement. The following illustrates what a SQL statement for a simple select query might look like in Access:. This example SQL statement reads "Select the data that is stored in the fields named E-mail Address and Company from the table named Contacts, specifically those records in which the value of the field City is Seattle.
If an identifier contains spaces or special characters such as "E-mail Address" , it must be enclosed in square brackets. A SELECT clause does not have to say which tables contain the fields, and it cannot specify any conditions that must be met by the data to be included. More information about how you use these clauses is presented in these additional articles:.
Like Microsoft Excel, Access lets you sort query results in a datasheet. An ORDER BY clause contains a list of the fields that you want to use for sorting, in the same order that you want to apply the sort operations. For example, suppose that you want your results sorted first by the value of the field Company in descending order, and — if there are records with the same value for Company — sorted next by the values in the field E-mail Address in ascending order.
Note: By default, Access sorts values in ascending order A-Z, smallest to largest. Use the DESC keyword to sort values in descending order instead.
Sometimes you want to work with summarized data, such as the total sales in a month, or the most expensive items in an inventory. For example, if you want your query to show the count of e-mail addresses listed for each company, your SELECT clause might resemble the following:.
The aggregate functions that you can use depend on the type of data that is in the field or expression that you want to use. For more information about the available aggregate functions, see the article SQL Aggregate Functions. If you want to use criteria to limit your results, but the field that you want to apply criteria to is used in an aggregate function, you cannot use a WHERE clause.
For example, if you only want the query to return rows if there are more than one e-mail addresses associated with the company, the HAVING clause might resemble the following:. When you want to review all the data that is returned by several similar select queries together, as a combined set, you use the UNION operator. The SELECT statements that you combine must have the same number of output fields, in the same order, and with the same or compatible data types.
When you run the query, data from each set of corresponding fields is combined into one output field, so that the query output has the same number of fields as each of the select statements.
Note: For the purposes of a union query, the Number and Text data types are compatible.
0コメント