You can use expressions for a wide variety of tasks in Microsoft Access, such as performing mathematical calculations, combining or extracting text, or validating data. This article provides overview information about expressions — when to use them, what their component parts are, and how they compare with Microsoft Excel formulas.
In this article
Overview of expressions
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 this expression in more detail.
1 DatePart is a function that examines a date and returns a specific portion. In this case, the first two arguments are used.
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.
Ways to use expressions
You can use expressions in the following ways:
-
Calculate values that do not exist directly in your data. You can calculate values in fields in tables and queries, and you can also calculate values in controls on forms and reports.
-
Define a default value for a table field or for a control on a form or report. These default values appear whenever you open a table, form, or report.
-
Create a validation rule to control what values users can enter in a field or control.
-
Define query criteria to limit results to a desired subset.
Calculate values
One of the most common ways to use expressions in Access is to calculate values that don't exist directly in your data. A column in a table or query that results from such a calculation is called a calculated field. You can create a calculated field that combines two or more table fields. For example, many tables store first and last names in separate fields. If you want to combine those first and last names and then display them in a single field, you can create a calculated field in the table or in a query:
[FirstName] & " " & [LastName].
In this case, the ampersands (&) combine the value in the FirstName field, a space character (a space enclosed in quotation marks), and the value in the LastName field.
Define a default value
You can also use expressions in Access to provide a default value for a field in a table or for a control. For example, to set the default value for a date field to the current date, in the Default Value property box for that field, you type:
Date()
Create a validation rule
In addition, you can use an expression to set a validation rule. For example, you can use a validation rule in a control or table field to require that the date that is entered is greater than or equal to the current date. In that case, you set the value in the Validation Rule property box to:
>= Date()
Define query criteria
Finally, you can use an expression to set criteria for a query. For example, suppose that you want to see product sales for the orders that were shipped within a certain time frame. You can enter criteria to define a date range, and Access returns only the rows that match the criteria. For example, your expression might look like this:
Between #1/1/2017# And #12/31/2017#
When you add criteria to the query and then run the query, it returns only those values that match the specified dates.
Examples of expressions
The following table lists some sample Access expressions and how they are typically used:
|
Expression |
Purpose |
|---|---|
|
=[RequiredDate]-[ShippedDate] |
Calculates the difference between the date values in two text box controls (called RequiredDate and ShippedDate) on a report. |
|
Date() |
Sets the default value for a Date/Time table field to the current date. |
|
Between #1/1/2017# And #12/31/2017# |
Specifies criteria for a Date/Time field in a query. |
|
=[Orders Subform].Form!OrderSubtotal |
Returns the value of the OrderSubtotal control on the Orders subform that is on the Orders form. |
|
>0 |
Sets a validation rule for a numeric field in a table — users must enter values greater than zero. |
Some expressions begin with the equal (=) operator, and others do not. When you calculate a value for a control on a form or report, you use the = operator to start the expression. In other instances, such as when you type an expression in a query or in the DefaultValue or ValidationRule property of a field or control, you don't use the = operator unless you are adding the expression to a Text field in a table. In some cases, such as when you add expressions to queries, Access removes the = operator automatically.
Components of expressions
An expression consists of a number of possible components that you can use, alone or in combination, to produce a result. These components include:
-
Identifiers The names of table fields or controls on forms or reports, or the properties of those fields or controls.
-
Operators For example, the + (plus) or - (minus).
-
Functions For example, SUM or AVG.
-
Constants Values that do not change — such as strings of text, or numbers that are not calculated by an expression.
-
Values Strings, such as "Enter a number between 1 and 10." or numbers, such as 1,254, that are used in operations.
The following sections describe these components in more detail.
Identifiers
An identifier is the name of a field, property, or control. You use an identifier in an expression to refer to the value that is associated with a field, property, or control. For example, consider the expression =[RequiredDate]-[ShippedDate]. This expression subtracts the value of the ShippedDate field or control from the value of the RequiredDate field or control. In this expression, both RequiredDate and ShippedDate serve as identifiers.
Operators
Access supports a variety of operators, including common arithmetic operators, such as +, -, * (multiply), and / (divide). You can also use comparison operators such as < (less than) or > (greater than) for comparing values, text operators such as & and + for concatenating (combining) text, logical operators such as Not and And for determining true or false values, and other operators specific to Access.
Functions
Functions are built-in procedures that you can use in your expressions. You use functions for a wide variety of operations, such as calculating values, manipulating text and dates, and summarizing data. For example, one commonly used function is DATE, which returns the current date. You can use the DATE function in a variety of ways, such as in an expression that sets the default value for a field in a table. In this example, whenever someone adds a new record, the value for the field is set to the current date by default.
Some functions require arguments. An argument is a value that provides input to the function. If a function requires more than one argument, you separate the arguments with a comma. For example, consider the DATE function in the following example expression:
=Format(Date(),"mmmm d, yyyy")
This example uses two arguments:
-
The first argument is the Date() function, which returns the current date. When there are no arguments, you still need to add the function parentheses.
-
The second argument "mmmm d, yyyy", which is separated from the first argument by a comma, specifies a text string to instruct the FORMAT function how to format the returned date value. Note that the text string must be enclosed in quotation marks.
This expression also illustrates that you can often nest the value returned by one function as an argument to another function. In this case, Date() acts as an argument.
Constants
A constant is an item whose value does not change while Access is running. The True, False, and Null constants are frequently used in expressions.
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.
When you use text string values, 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.
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.
Comparison of Access expressions and Excel formulas
Access expressions resemble Excel formulas in that both use similar elements to produce a result. Both Excel formulas and Access expressions contain one or more of the following:
-
Identifiers In Excel, identifiers are the names of individual cells or ranges of cells in a workbook, such as A1, B3:C6, or Sheet2!C32. In Access, identifiers are the names of table fields (such as [Contacts]![First Name]), controls on forms or reports (such as Forms![Task List]![Description]), or the properties of those fields or controls (such as Forms![Task List]![Description].ColumnWidth).
-
Operators In both Access and Excel, operators are used to compare values or to perform simple calculations on your data. Examples include + (plus) or - (minus).
-
Functions In both Access and Excel, functions and arguments are used to accomplish tasks that you can't do by using operators alone — for example, you can find the average of the values in a field, or convert the result of a calculation to a currency format. Examples of functions include SUM and STDEV. Arguments are values that provide information to functions. Note that Access and Excel both have many functions from which to choose, but the names of similar functions in the programs are sometimes different. For example, the AVERAGE function in Excel corresponds to the AVG function in Access.
-
Constants In both Access and Excel, constants are values that do not change — such numbers that are not calculated by using an expression.
-
Values In both Access and Excel, values are used in a similar manner.
Access expressions use operators and constants that are similar to those used in Excel formulas, but Access expressions use different identifiers and functions. Whereas Excel formulas are generally only used in worksheet cells, Access expressions are used in many places within Access for a wide variety of tasks, including the following:
-
Creating calculated controls on forms and reports
-
Creating calculated fields in tables and queries
-
Serving as criteria in queries
-
Validating data being entered into a field, or into a control on a form
-
Grouping data in reports
You can use either an Access expression or an Excel formula to calculate numeric or date/time values by using mathematical operators. For example, to calculate a discounted price for a customer, you can use the Excel formula =C2*(1-D2) or the Access expression = [Unit Price]*(1-[Discount]).
You can also use an Access expression or an Excel formula to combine, split, or otherwise manipulate strings by using string operators. For example, to combine a first and last name into one string, you can use the Excel formula =D3 & " " & D4 or the Access expression = [First Name] & " " & [Last Name].
