Access for 365 accessibility 2021 access 2019 access 2016 accessibility 2013 access 2010 access 2007 More...Less

You can use expressions for a wide variety of work in Access, such together performing mathematical calculations, combining or extracting text, or validating data. This article provides synopsis information around expressions — as soon as to use them, what your component parts are, and how lock compare through Excel formulas.

You are watching: A field that stores the value of a mathematical operation is

In this article

Overview that expressions

Think of the this way: as soon as you want access to carry out something, you have to speak that language. For example, suppose you desire to tell accessibility "Look in ~ the BirthDate field in the customers table and also tell me the year that the customer"s birth." You deserve to write this expression as:

DatePart("yyyy",!) This expression is composed of the DatePart function and two argument values: "yyyy" and also !.

Let"s examine this expression in more detail.


1 DatePart is a duty that examines a date and returns a details portion. In this case, the very first two arguments are used.

2 The interval dispute tells access which component of the day to return — in this case, "yyyy" tells accessibility that you want just the year part of the day returned.

3 The day argument tells accessibility where to look because that the date value — in this case, ! tells accessibility to look because that the date in the BirthDate ar of the customers table.

Ways to use expressions

You deserve to use expression in the complying with ways:

Calculate values that perform not exist directly in your data. You can calculate values in areas in tables and queries, and you can additionally calculate worths in controls on forms and also reports.

Define a default value for a table field or because that a manage on a kind or report. This default values show up whenever you open up a table, form, or report.

Create a validation rule to control what values individuals can enter in a ar or control.

Define questions criteria come limit results to a desired subset.

Calculate values

One of the most typical ways to usage expressions in accessibility is to calculate worths that don"t exist directly in her data. A shaft in a table or questions that outcomes from together a calculation is referred to as a calculate field. Girlfriend can create a calculated field that combines two or an ext table fields. Because that example, numerous tables store very first and last names in separate fields. If you desire to combine those very first and last names and then display screen them in a solitary field, girlfriend can produce a calculated ar in the table or in a query:

& " " & . In this case, the ampersands (&) combine the worth in the FirstName field, a an are character (a room enclosed in quotation marks), and the value in the LastName field.

Define a default value

You can additionally use expressions in access to provide a default worth for a ar in a table or because that a control. Because that example, to collection the default worth for a date field to the existing date, in the Default Value property box for that field, you type:

Date() Create a validation rule

In addition, you have the right to use one expression to collection a validation rule. For example, you have the right to use a validation dominion in a regulate or table ar to need that the date that is gotten in is higher than or equal to the present date. In the case, you collection the value in the Validation Rule property box to:

>= Date() Define questions criteria

Finally, you can use one expression to collection criteria because that a query. Because that example, mean that you desire to see product sales because that the orders that were shipped in ~ a particular time frame. You can enter criteria to define a day range, and access returns only the rows that match the criteria. Because that example, your expression could look like this:

Examples of expressions

The complying with table lists some sample access expressions and how castle are generally used:




Calculates the difference between the date values in two message box controls (called RequiredDate and also ShippedDate) ~ above a report.


Sets the default value for a Date/Time table field to the existing date.

Specifies criteria because that a Date/Time field in a query.


Returns the worth of the OrderSubtotal manage on the order subform the is top top the assignment form.


Sets a validation dominion for a numeric ar in a table — users must go into values higher than zero.

Some expressions start with the equal (=) operator, and also others carry out not. When you calculation a worth for a manage on a kind or report, you usage the = operator to start the expression. In other instances, together as once you form an expression in a query or in the DefaultValue or ValidationRule property of a field or control, friend don"t use the = operator unless you are adding the expression come a Text field in a table. In part cases, such as when you add expressions come queries, access removes the = operator automatically.

Components the expressions

An expression is composed of a number of possible contents that you can use, alone or in combination, to develop a result. These contents include:

Identifiers The name of table fields or controls on creates or reports, or the properties of those areas or controls.

Operators because that example, the + (plus) or - (minus).

Functions because that example, sum or AVG.

Constants values that execute not change — such together strings that text, or number that are not calculated by one expression.

Values Strings, such as "Enter a number between 1 and also 10." or numbers, such as 1,254, the are supplied in operations.

The following sections define these materials in much more detail.


An i would is the surname of a field, property, or control. You usage an i would in an expression to refer to the worth that is connected with a field, property, or control. Because that example, think about the expression =-. This expression subtracts the worth of the ShippedDate ar or control from the value of the RequiredDate field or control. In this expression, both RequiredDate and also ShippedDate offer as identifiers.


Access yellowcomic.coms a range of operators, including usual arithmetic operators, such as +, -, * (multiply), and also / (divide). Girlfriend can additionally use comparison operator such together (less than) or > (greater than) because that comparing values, message operators such together & and also + because that concatenating (combining) text, logical operator such as Not and also And for determining true or false values, and also other operators certain to Access.


Functions are built-in procedures that you deserve to use in your expressions. You use functions for a wide range of operations, such together calculating values, manipulating text and also dates, and also summarizing data. For example, one frequently used function is DATE, which return the present date. You can use the DATE duty in a range of ways, such together in one expression that sets the default worth for a field in a table. In this example, whenever who adds a brand-new record, the worth for the field is set to the present date by default.

Some functions require arguments. An discussion is a value that provides input to the function. If a role requires an ext than one argument, you separate the arguments with a comma. For example, take into consideration the DATE role in the following instance expression:

=Format(Date(),"mmmm d, yyyy") This example uses two arguments:

The first argument is the Date() function, which return the current date. As soon as there room no arguments, friend still need to include the duty parentheses.

The second argument "mmmm d, yyyy", which is separated indigenous the an initial argument through a comma, mentions a message string to instruct the FORMAT role how to format the returned date value. Note that the message string have to be fastened in quotation marks.

This expression also illustrates the you can often colony the value reverted by one duty as an discussion to another function. In this case, Date() acts together an argument.


A continuous is an item whose value does not adjust while accessibility is running. The True, False, and also Null constants are commonly used in expressions.


You have the right to use literal worths in your expressions, such together the number 1,254 or the wire "Enter a number between 1 and 10." friend can additionally use numeric values, which have the right to be a series of digits, including a sign and also a decimal point, if needed.

When you use text string values, location them within quotation marks to help make sure that accessibility interprets lock correctly. In part circumstances, accessibility supplies the quotation marks for you. For example, when you kind text in an expression because that a validation preeminence or because that query criteria, access surrounds your text strings through quotation marks automatically.

optimal of web page

Comparison of access expressions and Excel formulas

Access expressions resemble Excel recipe in that both use comparable elements to create a result. Both Excel recipe and accessibility expressions contain one or much more of the following:

Identifiers In Excel, identifiers room the surname of individual cell or varieties of cell in a workbook, such as A1, B3:C6, or Sheet2!C32. In Access, identifiers room the name of table fields (such as !), controls on forms or reports (such together Forms!!), or the properties of those areas or controls (such together Forms!!.ColumnWidth).

Operators In both accessibility and Excel, operators are offered to compare values or to perform an easy calculations on your data. Examples incorporate + (plus) or - (minus).

Functions In both access and Excel, functions and arguments are supplied to attain tasks the you can"t do by making use of operators alone — for example, friend can discover the typical of the values in a field, or convert the an outcome of a calculation to a currency format. Instances of functions encompass SUM and STDEV. Debates are worths that carry out information to functions. Keep in mind that accessibility and Excel both have countless functions native which come choose, however the names of similar functions in the program are occasionally different. For example, the AVERAGE duty in Excel coincides to the AVG role in Access.

Constants In both access and Excel, constants space values that do not change — such numbers that room not calculation by utilizing an expression.

Values In both accessibility and Excel, worths are supplied in a similar manner.

Access expressions use operators and also constants the are similar to those offered in Excel formulas, but access expressions use different identifiers and functions. Conversely, Excel formulas are usually only offered in worksheet cells, accessibility expressions are used in many places within accessibility for a wide selection of tasks, consisting of the following:

Creating calculation controls on forms and also reports

Creating calculated fields in tables and also queries

Serving as criteria in queries

Validating data being gone into into a field, or into a regulate on a form

Grouping data in reports

You deserve to use either an accessibility expression or one Excel formula to calculate numeric or date/time values by using mathematical operators. For example, to calculation a discounted price for a customer, you deserve to use the Excel formula =C2*(1-D2) or the access expression = *(1-).

See more: Miles From Los Angeles To Hawaii Usa, Please Wait

You can additionally use an accessibility expression or one Excel formula come combine, split, or otherwise manipulate strings by using string operators. Because that example, to integrate a first and critical name into one string, you deserve to use the Excel formula =D3 & " " & D4 or the access expression = & " " & .