Guide to expression syntax
When you use Microsoft Access 2010, you often encounter situations in which you need to work with values that do not reside directly in your data. For example, you might need to calculate sales tax on an order, or calculate the total value of the order itself. You can calculate these values by using expressions.
To use expressions, you must first write them by using proper syntax. Syntax is the set of rules by which the words and symbols in an expression are correctly combined.
Think of it this way: when you want Access to do something, you have to speak its language. For example, suppose you want to tell Access "Look at the BirthDate field in the Customers table and tell me the year of the customer's birth." You can write this expression as DatePart("yyyy",[Customers]![BirthDate]). This expression consists of the DatePart function and two argument values — "yyyy," and [Customers]![BirthDate].
Let's examine that expression in more detail.
1. DatePart is a function that examines a date and returns a specific portion.
2. The interval argument tells Access which part of the date to return — in this case, "yyyy" tells Access that you want only the year part of the date returned.
3. The date argument tells Access where to look for the date value — in this case, [Customers]![BirthDate] tells Access to look for the date in the BirthDate field of the Customers table.
As you can see, expressions in Access use a language that is, initially, a little bit hard to read. With a good understanding of expression syntax and a little practice, it becomes much easier.
There are a few key concepts that you should understand before you try to write expressions. This article introduces the concepts that you need to understand to use proper syntax, and discusses the syntax that you use in expressions.
This article does not address Structured Query Language (SQL) syntax, nor is it intended as a guide to Visual Basic for Applications (VBA) syntax.
In this article
Introduction
To build an expression, you combine identifiers by using functions, operators, and constants. Any valid expression must contain at least one function or at least one identifier, and can also contain constants or operators. You can also use an expression as part of another expression — typically as an argument of a function.
-
Identifiers in expressions The general form of an identifier in an expression is [Collection name]![Object name].[Property name].
Note: You only have to specify enough parts of an identifier to make it unique in the context of your expression. It is not uncommon for an identifier to take the form [Object name].
-
Functions in expressions The general form of an expression that uses a function is Function(argument, argument), where one of the arguments is usually an identifier or an expression.
Note: Some functions do not require arguments.
Before you use a particular function, review the corresponding Help article for specific information about that function's syntax.
-
Operators in expressions The general form of an expression that uses an operator is Identifier operator identifier. There are exceptions to this form, as detailed in the tables shown in the Operators section.
-
Constants in expressions The general form of an expression that uses a constant is Identifier comparison_operator constant.
Objects, collections, and properties
All of the tables, queries, forms, reports, and fields in an Access database are individually known as objects. Every object has a name. Some objects are already named, such as the Contacts table in a database created from the Contacts database template. When you create a new object, you give it a name.
The set of all members of a particular type of object is known as a collection. For example, the set of all tables in a database is a collection. Some objects that are a member of a collection in your database can also be collections that contain other objects. For example, a table object is a collection that contains field objects.
Objects have properties, which describe, and provide a way to change, the object's characteristics. For example, a query object has a Default View property that both describes and lets you specify how the query will appear when you run it.
The following diagram illustrates the relationship between collections, objects, and properties:
1. A collection
2. An object
3. A property
Identifiers
When you use an object, collection, or property in an expression, you refer to that element by using an identifier. An identifier includes the name of the element that you are identifying and also the name of the element to which it belongs. For instance, the identifier for a field includes the name of the field and the name of the table to which the field belongs. An example of such an identifier is found in the preceding example of an expression: [Customers]![BirthDate].
In some cases, the name of an element will work by itself as an identifier. This is true when the name of the element is unique in the context of the expression that you are creating. The rest of the identifier is implied by the context. For example, if you are designing a query that uses only one table, the field names alone will work as identifiers, because the field names in a table must be unique in that table. Because you are using only one table, the table name is implied in any identifier that you use in the query to refer to a field.
In other cases, you must be explicit about the parts of an identifier for a reference to work. This is true when an identifier is not unique in the context of the expression. When there is ambiguity, you must explicitly denote enough parts of the identifier to make it unique in context. For example, suppose you are designing a query that uses a table named Products and a table named Orders, and both tables have a field named ProductID. In such a case, the identifier that you use in the query to refer to either ProductID field must include the table name in addition to the field name — for example, [Products]![ProductID].
Identifier operators There are three operators that you can use in an identifier.
-
The bang operator (!)
-
The dot operator (.)
-
The square brackets operator ([ ])
You use these operators by surrounding each part of the identifier with square brackets, and then joining them by using either a bang or a dot operator. For example, an identifier for a field named Last Name in a table named Employees can be expressed as [Employees]![Last Name]. The bang operator tells Access that what follows is an object that belongs to the collection that precedes the bang operator. In this case, [Last Name] is a field object that belongs to the collection [Employees], which is itself a table object.
Note: Strictly speaking, you do not always have to type square brackets around an identifier or partial identifier. If there are no spaces or other special characters in the identifier, Access automatically adds the brackets when it reads the expression. However, it is a good practice to type the brackets yourself — this helps you to avoid errors, and also functions as a visual clue that a particular part of an expression is an identifier.
Functions, operators, and constants
To create an expression, you need more than identifiers — you need to perform an action of some sort. You use functions, operators, and constants to perform actions in an expression.
Functions
A function is a procedure that you can use in an expression. Some functions, such as Date, do not require any input in order for them to work. Most functions, however, do require input, called arguments. In the example at the beginning of this article, the DatePart function uses two arguments: an interval argument (with a value of "yyyy") and a date argument (with a value of [Customers]![BirthDate]). The DatePart function requires at least these two arguments (interval and date), but can accept up to four arguments.
Note: When a function has more than one argument, those arguments are separated by commas.
The following list shows some functions that are commonly used in expressions. Click the link for each function for more information about the syntax that you use with that function.
-
Date The Date function is used to insert the current system date into an expression. It is commonly used in conjunction with the Format function, and is also used in conjunction with field identifiers for fields that contain date/time data.
-
DatePart The DatePart function is used to determine or extract part of a date — usually a date that is obtained from a field identifier, but sometimes a date value that is returned by another function, such as Date.
-
DateDiff The DateDiff function is used to determine the difference between two dates — usually between a date that is obtained from a field identifier and a date that is obtained by using the Date function.
-
Format The Format function is used to apply a format to an identifier, and also to apply a format to the results of another function.
-
IIf The IIf function is used to evaluate an expression as true or false, and then return one specified value if the expression evaluates as true, and a different specified value if the expression evaluates as false.
-
InStr The InStr function is used to search for the position of a character or string within another string. The string that is searched is usually obtained from a field identifier.
-
Left, Mid, and Right These functions are used to extract characters from a string, starting with the leftmost character (Left), a specific position in the middle (Mid), or with the rightmost character (Right). They are commonly used in conjunction with the InStr function. The string from which these functions extract characters is usually obtained from a field identifier.
For more information about these and other functions, see the Access 2010 Developer Center (MSDN) or the article Get help for built-in functions, properties, macro actions, and SQL keywords.
Operators
An operator is a word or symbol that indicates a specific arithmetic or logical relationship between the other elements of an expression. Operators can be:
-
Arithmetic, such as the plus sign (+).
-
Comparison operators, such as the equal sign (=).
-
Logical operators, such as Not.
Operators are generally used to indicate a relationship between two identifiers. The following tables describe the operators that you can use in Access expressions.
Arithmetic operators
You use the arithmetic operators to calculate a value from two or more numbers or to change the sign of a number from positive to negative.
| Operator | Purpose | Example |
| + | Sum two numbers. | [Subtotal]+[SalesTax] |
| - | Find the difference between two numbers or indicate the negative value of a number. | [Price]-[Discount] |
| * | Multiply two numbers. | [Quantity]*[Price] |
| / | Divide the first number by the second number. | [Total]/[ItemCount] |
| \ | Round both numbers to integers, divide the first number by the second number, and then truncate the result to an integer. | [Registered]\[Rooms] |
| Mod | Divide the first number by the second number and return only the remainder. | [Registered] Mod [Rooms] |
| ^ | Raise a number to the power of an exponent. | Number ^ Exponent |
Comparison operators
You use the comparison operators to compare values and return a result that is either true, false, or Null (an unknown value).
| Operator | Purpose |
| < | Determine if the first value is less than the second value. |
| <= | Determine if the first value is less than or equal to the second value. |
| > | Determine if the first value is greater than the second value. |
| >= | Determine if the first value is greater than or equal to the second value. |
| = | Determine if the first value is equal to the second value. |
| <> | Determine if the first value is not equal to the second value. |
In all cases, if either the first value or the second value is Null, the result is then also Null. Because Null represents an unknown value, the result of any comparison with Null is also unknown.
Logical operators
You use the logical operators to combine two values and return either a true, false, or Null result. You might also see the logical operators referred to as Boolean operators.
| Operator | Usage | Description |
| And | Expr1 And Expr2 | True when Expr1 and Expr2 are true. |
| Or | Expr1 Or Expr2 | True when either Expr1 or Expr2 is true. |
| Eqv | Expr1 Eqv Expr2 | True when both Expr1 and Expr2 are true or both Expr1 and Expr2 are false. |
| Not | Not Expr | True when Expr is not true. |
| Xor | Expr1 Xor Expr2 | True when either Expr1 is true, or Expr2 is true, but not both. |
Concatenation operators
You use the concatenation operators to combine two text values into one string.
| Operator | Usage | Description |
| & | string1 & string2 | Combines two strings to form one string. |
| + | string1 + string2 | Combines two strings to form one string and propagates null values. |
Special operators
You use the special operators as described in the following table.
| Operator | Description |
| Is Null or Is Not Null | Determines whether a value is Null or Not Null. |
| Like "pattern" | Matches string values by using wildcard operators ? and *. |
| Between val1 And val2 | Determines whether a numeric or date value falls within a range. |
| In(string1,string2...) | Determines whether a string value is contained within a set of string values. |
Constants
A constant is a known value that does not change and that you can use in an expression. There are four commonly used constants in Access:
-
True Indicates something that is logically true.
-
False Indicates something that is logically false.
-
Null Indicates the lack of a known value.
-
"" (empty string) Indicates a value that is known to be empty.
Constants can be used as arguments to a function, and can be used in an expression as part of a criterion. For example, you can use the empty string constant ("") as part of a criterion for a column in a query to evaluate the field values for that column, by entering the following as the criterion: <>"". In this example, <> is an operator and "" is a constant. Used together, they indicate that the identifier to which they are applied should be compared to an empty string. The expression that results is true when the identifier's value is anything other than an empty string.
Note: Be careful when using the Null constant. In most cases, using Null in conjunction with a comparison operator will result in an error. If you want to compare a value to Null in an expression, use the Is Null or the Is Not Null operator.
For more information about using expressions, see the article Create an expression.
No comments:
Post a Comment