Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.
You can deploy expressions for a numerous range of tasks in Microsoft Access, like running mathematical calculations, combining or extracting text, or validating data. This article offers summary information about expressions — when they must be used, what their component parts are, and how they compare with Microsoft Excel formulas.
In this article
Overview of expressions
Envision it this way: anytime 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 compose this expression as:
This expression includes 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 employ expressions in the following ways:
- Calculate values that are absent 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 display whenever you open a table, form, or report.
- Create a validation rule to dictate what values users can enter in a field or control.
- Define query criteria to restrict results to a desired subset.
One of the most frequent ways to use expressions in Access is to calculate values that are missing directly in your data. A column in a table or query that results from such a calculation is known as a calculated field. You can design a calculated field that merges 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 even use expressions in Access to offer a standard 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:
Create a validation rule
In addition, you can apply an expression to define 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:
Define query criteria
Finally, you can employ an expression to determine 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#
Once you add criteria to the query and then perform the query, it returns merely those values that correspond to the defined dates.
Examples of expressions
The following table lists some sample Access expressions and how they are typically used:
|=[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 start with the equal (=) operator, and others do not. After you calculate a value for a control on a form or report, you utilise the = operator to begin the expression. In other cases, like once you enter 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 inserting the expression to a Text field in a table. In some situations, like when you add expressions to queries, Access erases the = operator instantly.
Components of expressions
An expression comprises multiple sets of possible components that you can use, independently or in combination, to formulate a result. These components are as follows:
- 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 remain static — such as strings of text, or numbers that are not calculated by an expression.
- Values – Strings, like “Enter a number between 1 and 10.” or numbers, such as 1,254, that are placed in operations.
The following sections outline these components in greater detail.
An identifier is the name of a field, property, or control. You apply an identifier in an expression to allude to the value that is linked 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 act as identifiers.
Access facilitates many operators, including common arithmetic operators, such as +, -, * (multiply), and / (divide). You can even utilise 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 confirming true or false values, and other operators tailored to Access.
Functions are ingrained procedures that you can employ in your expressions. You use functions for a broad myriad of operations, like calculating values, manipulating text and dates, and summarising data. For example, one commonly used function is DATE, which returns the current date. You can use the DATE function in many different 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 need arguments. An argument is a value that supplies input to the function. If a function mandates several arguments, you divide 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. Anytime no arguments are present, you still must append the function parentheses.
- The second argument “mmmm d, yyyy”, which is split from the first argument by a comma, defines a text string to command the FORMAT function how to format the returned date value. Note that the text string has to be contained within quotation marks.
This expression even displays that you can typically nest the value returned by one function as an argument to another function. In this case, Date() serves as an argument.
A constant is an item whose value stays the same while Access is running. The True, False, and Null constants are commonly used in expressions.
You can assign literal values in your expressions, such as the number 1,254 or the string “Enter a number between 1 and 10.” You can even utilise numeric values, which can be a set of digits, including a sign and a decimal point, if necessary.
Once you use text string values, position them within quotation marks to aid complete assurance that Access deduces them accurately. In some cases, Access provides 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, surround 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 encounters a valid date/time value that is enveloped in # characters, it promptly treats the value as a Date/Time data type.
Comparison of Access expressions and Excel formulas
Access expressions replicate Excel formulas in that both use related elements to create a result. Both Excel formulas and Access expressions include one or more of the following:
- Identifiers – In Excel, identifiers refer to 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 employed to compare values or to run basic calculations on your data. Examples include + (plus) or – (minus).
- Functions – In both Access and Excel, functions and arguments are applied to achieve tasks that you struggle to fulfil single-handledly through using only operators — 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 offer information to functions. Note that Access and Excel both have many functions from which to select, but the names of similar functions in the programmes are often contrasting. For example, the AVERAGE function in Excel corresponds to the AVG function in Access.
- Constants – In both Access and Excel, constants are values that remain fixed — such numbers that are skipped form being calculated by using an expression.
- Values – In both Access and Excel, values are utilised in a relatable manner.
Access expressions apply operators and constants that are alike those used in Excel formulas, but Access expressions rely on other identifiers and functions. Whereas Excel formulas are mainly only used in worksheet cells, Access expressions are used in several areas within Access for a broader array 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 applying 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 even use an Access expression or an Excel formula to merge, divide, or otherwise control 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].
This information was compiled using information courtesy of © Microsoft 2020. All rights reserved.