When you use Microsoft Access, you often need to work with values that are not directly in your data. For example, you want 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 write them by using proper syntax. Syntax is the set of rules by which the words and symbols in an expression are correctly combined. Initially, expressions in Access are a little bit hard to read. But with a good understanding of expression syntax and a little practice, it becomes much easier.
In this article
Overview of expressions
An expression is a combination of some or all of the following: built-in or user-defined functions, identifiers, operators, values, and constants that evaluate to a single value.
For example, the following expression contains common components:
=Sum([Purchase Price])*0.08
-
Sum() is a built-in function
-
[Purchase Price] is an identifier
-
* is a mathematical operator
-
0.08 is a constant
This expression can be used in a text box in a form footer or report footer to calculate sales tax for a group of items.
Expressions can be much more complex or much simpler than this example. For example, this Boolean expression consists of just an operator and a constant:
>0
This expression returns:
-
True when it is compared to a number that is greater than 0.
-
False when it is compared to a number that is less than 0.
You can use this expression in the Validation Rule property of a control or table field to ensure that only positive values are entered. Expressions are used in many places to perform calculations, manipulate characters, or test data. Tables, queries, forms, reports, and macros all have properties that accept an expression. For example, you can use expressions in the Control Source and Default Value properties for a control. You can also use expressions in the Validation Rule property for a table field.
Components of expressions
To build an expression, you combine identifiers by using functions, operators, constants, and values. 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. This is called nesting an expression.
-
Identifiers The general form is:
[Collection name]![Object name].[Property name]
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 The general form is:
Function(argument, argument)
One of the arguments is usually an identifier or an expression. Some functions do not require arguments. Before you use a particular function, review that function's syntax. For more information, see Functions (arranged by category).
-
Operators The general form is:
Identifier operator identifier
There are exceptions to this form, as detailed in the Operators section.
-
Constants The general form is:
Identifier comparison_operator constant
-
Values Values can occur in many locations in an expression.
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 Microsoft Office Access Contacts 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:
|
|
3 A property 2 An object 1 A collection |
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:
[Customers]![BirthDate]
In some cases, the name of an element works 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.
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, constants, and values
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 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.
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.
-
The Date function is used to insert the current system date into an expression. It is commonly used with the Format function, and is also used with field identifiers for fields that contain date/time data.
=Date()
-
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.
DatePart ( "yyyy", Date())
-
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.
=DateDiff(“d”, Now(), [Orders].[ReceiveBefore])-10
-
The Format function is used to apply a format to an identifier and the results of another function.
Format([Date],"ww")=Format(Now(),"ww")-1
-
The IIf function is used to evaluate an expression as true or false, and then return one value if the expression evaluates as true, and a different value if the expression evaluates as false.
=IIf([CountryRegion]="Italy", "Italian", "Some other language")
-
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.
InStr(1,[IPAddress],".")
-
The Left, Mid, and Right 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 with the InStr function. The string from which these functions extract characters is usually obtained from a field identifier.
Left([ProductName], 1) Right([AssetCode], 2) Mid([Phone],2,3)
For a list of functions, see Functions (arranged by category).
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, such as the equal sign (=).
-
Logical, such as Not.
-
Concatenation, such as &.
-
Special, such as Like.
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
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
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
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
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
You use the special operators as described in the following table.
|
Operator |
Description |
For More Information |
|---|---|---|
|
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.
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.
Values
You can use literal values in your expressions, such as the number 1,254 or the string "Enter a number between 1 and 10." You can also use numeric values, which can be a series of digits, including a sign and a decimal point, if needed. In the absence of a sign, Access assumes a positive value. To make a value negative, include the minus sign (-). You can also use scientific notation. To do so, include "E" or "e" and the sign of the exponent (for example, 1.0E-6).
When you use text strings, place them within quotation marks to help make sure that Access interprets them correctly. In some circumstances, Access supplies the quotation marks for you. For example, when you type text in an expression for a validation rule or for query criteria, Access surrounds your text strings with quotation marks automatically.
For example, if you type the text Paris, Access displays "Paris" in the expression. If you want an expression to produce a string that is actually enclosed in quotation marks, you enclose the nested string either in single (') quotation marks or within three sets of double (") quotation marks. For example, the following expressions are equivalent:
Forms![Contacts]![City]. DefaultValue = ' "Paris" '
Forms![Contacts]![City].DefaultValue = " " "Paris" " "
To use date/time values, enclose the values in pound signs (#). For example, #3-7-17#, #7-Mar-17#, and #Mar-7-2017# are all valid date/time values. When Access encounters a valid date/time value that is enclosed in # characters, it automatically treats the value as a Date/Time data type.
The nesting limit for expressions in a web database is 65
Expressions nested more than 65 levels deep won’t work in the browser, so you shouldn’t use any such expressions in an Access web database. You won’t get any error messages – the expression just won’t work.
The use of the &, AND, and OR operators can create additional nesting levels on the server that aren't reflected in the Access client. For example the expression "a" & "b" & "c" is not nested in the Expression Builder, but in SharePoint it becomes concatenate.Db("a", concatenate.Db("b", "c") ). This translation creates one level of nesting. Using many consecutive &, AND, or OR operators in a single expression can cause you to exceed the server's nesting limit of 65, at which point the expression won’t work in the browser.
