Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.
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() 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:
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 identifierThere 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 depicts the relationship between collections, objects, and properties:
|3 A property|
2 An object
1 A collection
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:
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:
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 make an expression, you must have more than identifiers — you have to impose an action of some kind. You use functions, operators, and constants to perform actions in an expression.
A function is a method 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.
- 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.
- 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.
- 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).
An operator is a word or symbol that represents a distinct 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 normally used to reflect a relationship between two identifiers. The following tables describe the operators that you can use in Access expressions.
You apply the arithmetic operators to calculate a value from two or more numbers or to change the sign of a number from positive to negative.
|+||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|
You use the comparison operators to compare values and return a result that is either true, false, or Null (an unknown value).
|<||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.
You use the logical operators to integrate two values and return either a true, false, or Null result. You may also notice the logical operators referred to as Boolean operators.
|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.|
You use the concatenation operators to merge two text values into one string.
|&||string1 & string2||Combines two strings to form one string.|
|+||string1 + string2||Combines two strings to form one string and propagates null values.|
You use the special operators as outlined in the following table.
|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 *.||Like Operator|
|Between val1 And val2||Determines whether a numeric or date value falls within a range.||Between…And Operator|
|In(string1,string2…)||Determines whether a string value is contained within a set of string values.||In Operator|
A constant is a known value that remains fixed and that you can apply 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 typing the following as the criterion: <>””. In this example, <> is an operator and “” is a constant. Used together, they represent that the identifier to which they are relayed should be benchmarked to an empty string. The expression that arises is true in the event that the identifier’s value is something except an empty string.
Be cautious when using the Null constant. In most cases, using Null alongside a comparison operator will cause an error. If you want to compare a value to Null in an expression, use the Is Null or the Is Not Null operator.
You can employ literal values in your expressions, such as the number 1,254 or the string “Enter a number between 1 and 10.” You can even deploy 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 presumes a positive value. To change a value to a negative, add the minus sign (–). You can even use scientific notation. To do so, include “E” or “e” and the sign of the exponent (for example, 1.0E-6).
Once you use text strings, locate them within quotation marks to help ensure that Access interprets them correctly. In some circumstances, Access offers 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 apply date/time values, encompass the values in pound signs (#). For example, #3-7-17#, #7-Mar-17#, and #Mar-7-2017# are all valid date/time values. Once Access meets a valid date/time value that is enclosed in # characters, it instantly identifies the value as a Date/Time data type.
The nesting limit for expressions in a web database is 65
Expressions nested over 65 levels deep won’t work in the browser, so you shouldn’t use any of these expressions in an Access web database. You won’t receive any error messages – the expression simply breaks down.
The use of the &, AND, and OR operators can make extra 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 designs one level of nesting. Applying many consecutive &, AND, or OR operators in one expression can result in you to surpass the server’s nesting limit of 65, at which point the expression will cease in the browser.
This information was compiled using information courtesy of © Microsoft 2020. All rights reserved.