Examples of expressions

Applies To: Access 2016 , Access 2013 , Access 2010 , Access 2007

This article provides examples of expressions in Access. An expression is a combination of

mathematical or logical operators, constants, functions, table fields, controls, and properties
that evaluates to a single value. You can use expressions in Access to calculate values,
validate data, and set a default value for a field or control.

Note: Although this article provides basic steps for creating expressions, it is not a
comprehensive guide for using the tools that Access provides for creating expressions. For
more information about creating expressions, see the article Create an expression.

In this article
Understand expressions

Examples of expressions used in forms and reports

Examples of expressions used in queries and filters

Examples of default value expressions

Examples of field validation rule expressions

Examples of macro condition expressions

Understand expressions
In Access, the term expression is synonymous with formula. An expression consists of a
number of possible elements that you can use, alone or in combination, to produce a result.
Those elements include:

 Identifiers — the names of table fields or controls on forms or reports, or the

properties of those fields or controls
 Operators, such as + (plus) or - (minus)
 Functions, such as SUM or AVG
 Constants — values that do not change — such as strings of text, or numbers that are
not calculated by an expression.

You can use expressions in a number of ways — to perform a calculation, retrieve the value
of a control, or supply criteria to a query, just to name a few.

For more information about how and where to use expressions, see the article Create an
expression.

Top of Page
Examples of expressions used in forms and reports
The tables in this section provide examples of expressions that calculate a value in a control
located on a form or report. To create a calculated control, you enter an expression in the
ControlSource property of the control, instead of in a table field or query.

The following steps explain how to enter an expression in a text box control on an existing
form or report.

Create a calculated control

1. In the Navigation Pane, right-click the form or report that you want to change and
click Design View on the shortcut menu.
2. On the form or report, right-click the text box control that you want to change (not the
label associated with the text box), and then click Properties on the shortcut menu.
3. If necessary, click either the All tab or the Data tab. Both tabs provide the Control
Source property.
4. Click the box next to the Control Source property and type your expression. For
example, you can copy and paste an expression from the Expression column in the
table in the following section.
5. Close the property sheet.

Expressions that combine or manipulate text

The expressions in the following table use the & (ampersand) and the + (plus) operators to
combine text strings, built-in functions to manipulate a text string, or otherwise operate on
text to create a calculated control.

Expression Result
="N/A" Displays N/A.
Displays the values that reside in table fields called
FirstName and LastName. In this example, the &
=[FirstName] & " " & [LastName] operator is used to combine the FirstName field, a space
character (enclosed in quotation marks), and the
LastName field.
Uses the Left function to display the first character of
=Left([ProductName], 1)
the value of a field or control called ProductName.
Uses the Right function to display the last 2 characters
=Right([AssetCode], 2)
of the value in a field or control called AssetCode.
Uses the Trim function to display the value of the
=Trim([Address]) Address control, removing any leading or trailing
spaces.
Uses the IIf function to display the values of the City
=IIf(IsNull([Region]), [City] & " " and PostalCode controls if the value in the Region
& [PostalCode], [City] & " " & control is null; otherwise, it displays the values of the
[Region] & " " & [PostalCode]) City, Region, and PostalCode controls, separated by
spaces.
Expression Result
Uses the + operator and null propagation to display the
values of the City and PostalCode controls if the value
in the Region field or control is null; otherwise, it
displays the values of the City, Region, and PostalCode
=[City] & (" " + [Region]) & " " & fields or controls, separated by spaces.
[PostalCode]
Null propagation means that if any component of an
expression is null, the entire expression is also null. The
+ operator supports null propagation; the & operator
does not.

Expressions in headers and footers

You use the Page and the Pages properties to display or print page numbers in forms or
reports. The Page and Pages properties are available only during printing or print preview, so
they do not appear on the property sheet for the form or report. Typically, you use these
properties by placing a text box in the header or footer section of the form or report, and then
using an expression, such as the ones shown in the following table.

For more information about using headers and footers in forms and reports, see the article
Insert page numbers into a form or report.

Expression Example result

=[Page] 1
="Page " & [Page] Page 1
="Page " & [Page] & " of " & [Pages] Page 1 of 3
=[Page] & " of " & [Pages] & " Pages" 1 of 3 Pages
=[Page] & "/" & [Pages] & " Pages" 1/3 Pages
=[Country/region] & " - " & [Page] UK - 1
=Format([Page], "000") 001
="Printed on: " & Date() Printed on: 12/31/07

Expressions that perform arithmetic operations

You can use expressions to add, subtract, multiply, and divide the values in two or more
fields or controls. You can also use expressions to perform arithmetic operations on dates.
For example, suppose you have a Date/Time table field named RequiredDate. In the field, or
in a control bound to the field, the expression =[RequiredDate] - 2 returns a date/time value
equal to two days before the current values in the RequiredDate field.

Expression Result
The sum of the values of the Subtotal and Freight
=[Subtotal]+[Freight]
fields or controls.
The interval between the date values of the
=[RequiredDate]-[ShippedDate]
RequiredDate and ShippedDate fields or controls.
Expression Result
The product of the value of the Price field or
=[Price]*1.06 control and 1.06 (adds 6 percent to the Price
value).
The product of the values of the Quantity and
=[Quantity]*[Price]
Price fields or controls.
The quotient of the values of the EmployeeTotal
=[EmployeeTotal]/[CountryRegionTotal]
and CountryRegionTotal fields or controls.

Note: When you use an arithmetic operator (+, -, *, and /) in an expression, and the value of
one of the controls in the expression is null, the result of the entire expression will be null —
this is known as Null propagation. If any records in one of the controls that you use in the
expression might have a null value, you can avoid Null propagation by converting the null
value to zero by using the Nz function — for example, =Nz([Subtotal])+Nz([Freight]).

For more information about the function, see the article Nz Function.

Expressions that refer to values in other fields or controls

Sometimes, you need a value that exists somewhere else, such as in a field or control on
another form or report. You can use an expression to return the value from another field or
control.

The following table lists examples of expressions that you can use in calculated controls on
forms.

Expression Result
The value of the OrderID control on the Orders
=Forms![Orders]![OrderID]
form.
=Forms![Orders]![Orders The value of the OrderSubtotal control on the
Subform].Form![OrderSubtotal] subform named Orders Subform on the Orders form.
The value of the third column in ProductID, a
multiple-column list box on the subform named
=Forms![Orders]![Orders
Orders Subform on the Orders form. (Note that 0
Subform]![ProductID].Column(2)
refers to the first column, 1 refers to the second
column, and so on.)
The product of the value of the Price control on the
=Forms![Orders]![Orders subform named Orders Subform on the Orders form
Subform]![Price] * 1.06 and 1.06 (adds 6 percent to the value of the Price
control).
The value of the OrderID control on the main or
=Parent![OrderID]
parent form of the current subform.

The expressions in the following table show some ways to use calculated controls on reports.
The expressions reference the Report property.

For more information about that property, see the article Report Property.
Expression Result
The value of a control called "OrderID" in a
=Report![Invoice]![OrderID]
report called "Invoice."
The value of the SalesTotal control on the
=Report![Summary]![Summary
subreport named Summary Subreport on the
Subreport]![SalesTotal]
Summary report.
The value of the OrderID control on the main or
=Parent![OrderID]
parent report of the current subreport.

Expressions that count, sum, and average values

You can use a type of function called an aggregate function to calculate values for one or
more fields or controls. For example, you can calculate a group total for the group footer in a
report, or an order subtotal for line items on a form. You can also count the number of items
in one or more fields or calculate an average value.

The expressions in the following table show some of the ways to use functions such as Avg,
Count, and Sum.

Expression Description
Uses the Avg function to display the average of the values of a
=Avg([Freight])
table field or control named "Freight."
Uses the Count function to display the number of records in the
=Count([OrderID])
OrderID control.
Uses the Sum function to display the sum of the values of the
=Sum([Sales])
Sales control.
Uses the Sum function to display the sum of the product of the
=Sum([Quantity]*[Price])
values of the Quantity and the Price controls.
Displays the percentage of sales, determined by dividing the
value of the Sales control by the sum of all the values of the
Sales control.
=[Sales]/Sum([Sales])*100
Note: If you set the Format property of the control to Percent,
do not include *100 in the expression.

For more information about using aggregate functions and totaling the values in field and
columns, see the articles Sum data by using a query, Count data by using a query, Count the
rows in a datasheet, and Display column totals in a datasheet.

Expressions that count, sum, and look up values selectively

by using domain aggregate functions
You use a type of function called a domain aggregate function when you need to sum or
count values selectively. A "domain" consists of one or more fields in one or more tables, or
one or more controls on one or more forms or reports. For example, you can match the values
in a table field with the values in a control on a form.
Expression Description
Uses the DLookup function to
return the value of the ContactName
=DLookup("[ContactName]", "[Suppliers]", field in the Suppliers table where
"[SupplierID] = " & the value of the SupplierID field in
Forms("Suppliers")("[SupplierID]")) the table matches the value of the
SupplierID control on the Suppliers
form.
Uses the DLookup function to
return the value of the ContactName
=DLookup("[ContactName]", "[Suppliers]", field in the Suppliers table where
"[SupplierID] = " & Forms![New the value of the SupplierID field in
Suppliers]![SupplierID]) the table matches the value of the
SupplierID control on the New
Suppliers form.
Uses the DSum function to return
the sum total of the values in the
=DSum("[OrderAmount]", "[Orders]", "[CustomerID]
OrderAmount field in the Orders
= 'RATTC'")
table where the CustomerID is
RATTC.
Uses the DCount function to return
the number of Yes values in the
=DCount("[Retired]","[Assets]","[Retired]=Yes")
Retired field (a Yes/No field) in the
Assets table.

Expressions that manipulate and calculate dates

Tracking dates and times is a fundamental database activity. For example, you can calculate
how many days have elapsed since the invoice date to age your accounts receivable. You can
format dates and times in numerous ways, as shown in the following table.

Expression Description
Uses the Date function to display the current date in the form of
mm-dd-yy, where mm is the month (1 through 12), dd is the day
=Date()
(1 through 31), and yy is the last two digits of the year (1980
through 2099).
Uses the Format function to display the week number of the year
=Format(Now(), "ww")
for the current date, where ww represents weeks 1 through 53.
=DatePart("yyyy", Uses the DatePart function to display the four-digit year of the
[OrderDate]) value of the OrderDate control.
=DateAdd("y", -10, Uses the DateAdd function to display a date that is 10 days before
[PromisedDate]) the value of the PromisedDate control.
=DateDiff("d", Uses the DateDiff function to display the number of days'
[OrderDate], difference between the values of the OrderDate and ShippedDate
[ShippedDate]) controls.
Uses arithmetic operations on dates to calculate the date 30 days
=[InvoiceDate] + 30
after the date in the InvoiceDate field or control.
Conditional expressions that return one of two possible
values
The example expressions in the following table use the IIf function to return one of two
possible values. You pass the IIf function three arguments: The first argument is an
expression that must return a True or False value. The second argument is the value to return
if the expression is true, and the third argument is the value to return if the expression is false.

Expression Description
Uses the IIf (Immediate If) function to display
=IIf([Confirmed] = "Yes", "Order the message "Order Confirmed" if the value of
Confirmed", "Order Not Confirmed") the Confirmed control is Yes; otherwise, it
displays the message "Order Not Confirmed."
Uses the IIf and IsNull functions to display an
=IIf(IsNull([Country/region]), " ", empty string if the value of the Country/region
[Country]) control is null; otherwise, it displays the value of
the Country/region control.
Uses the IIf and IsNull functions to display the
=IIf(IsNull([Region]), [City] & " " & values of the City and PostalCode controls if the
[PostalCode], [City] & " " & [Region] & " value in the Region control is null; otherwise, it
" & [PostalCode]) displays the values of the City, Region, and
PostalCode fields or controls.
Uses the IIf and IsNull functions to display the
=IIf(IsNull([RequiredDate]) Or message "Check for a missing date" if the result
IsNull([ShippedDate]), "Check for a of subtracting ShippedDate from RequiredDate is
missing date", [RequiredDate] - null; otherwise, it displays the interval between
[ShippedDate]) the date values of the RequiredDate and
ShippedDate controls.

Top of Page

Examples of expressions used in queries and filters

This section contains examples of expressions that you can use to create a calculated field in
a query or to supply criteria to a query. A calculated field is a column in a query that results
from an expression. For example, you can calculate a value, combine text values such as first
and last names, or format a portion of a date.

You use criteria in a query to limit the records that you work with. For example, you can use
the Between operator to supply a starting and ending date and limit the results of your query
to orders that were shipped between those dates.

The following sections explain how to add a calculated field to a query and provide examples
of expressions for use in queries.

Add a calculated field in query Design view

 1. In the Navigation Pane, right-click the query that you want to change and click
Design View on the shortcut menu.
2. Click the Field cell in the column where you want to create the calculated field. You
can enter a name for the field followed by a colon, or you can type your expression. If
you do not enter a name, Access adds Exprn:, where n is a sequential number.
3. Type your expression.

-or-

On the Design tab, in the Query Setup group, click Builder to start the Expression
Builder.

For more information about using the Expression Builder, see the article Create an
expression.

Expressions that manipulate text in a query or filter

The expressions in the following table use the & and + operators to combine text strings, use
built-in functions to operate on a text string, or otherwise operate on text to create a
calculated field.

Expression Description
Creates a field called FullName that displays the
FullName: [FirstName] & " " &
values in the FirstName and LastName fields,
[LastName]
separated by a space.
Creates a field called Address2 that displays the
Address2: [City] & " " & [Region] & " "
values in the City, Region, and PostalCode fields,
& [PostalCode]
separated by spaces.
Creates a field called ProductInitial, and then uses
the Left function to display, in the ProductInitial
ProductInitial:Left([ProductName], 1)
field, the first character of the value in the
ProductName field.
Creates a field called TypeCode, and then uses the
TypeCode: Right([AssetCode], 2) Right function to display the last two characters of
the values in the AssetCode field.
Creates a field called AreaCode, and then uses the
Mid function to display the three characters
AreaCode: Mid([Phone],2,3)
starting with the second character of the value in
the Phone field.

Expressions that perform arithmetic operations in

calculated fields
You can use expressions to add, subtract, multiply, and divide the values in two or more
fields or controls. You can also perform arithmetic operations on dates. For example, suppose
you have a Date/Time field called RequiredDate. The expression =[RequiredDate] - 2
returns a Date/Time value equal to two days before the value in the RequiredDate field.
Expression Description
Creates a field called PrimeFreight, and then displays
PrimeFreight: [Freight] * 1.1
freight charges plus 10 percent in the field.
Creates a field called OrderAmount, and then displays
OrderAmount: [Quantity] *
the product of the values in the Quantity and UnitPrice
[UnitPrice]
fields.
Creates a field called LeadTime, and then displays the
LeadTime: [RequiredDate] -
difference between the values in the RequiredDate and
[ShippedDate]
ShippedDate fields.
Creates a field called TotalStock, and then displays the
TotalStock:
sum of the values in the UnitsInStock and
[UnitsInStock]+[UnitsOnOrder]
UnitsOnOrder fields.
Creates a field called FreightPercentage, and then
displays the percentage of freight charges in each
subtotal. This expression uses the Sum function to total
the values in the Freight field, and then divides those
totals by the sum of the values in the Subtotal field.

To use this expression, you must convert your select

FreightPercentage:
query into a Totals query because you need to use the
Sum([Freight])/Sum([Subtotal])
Total row in the design grid, and you must set the
*100
Total cell for this field to Expression.

For more information about creating a Totals query, see

the article Sum data by using a query.

If you set the Format property of the field to Percent,

do not include *100.

For more information about using aggregate functions and totaling the values in field and
columns, see the articles Sum data by using a query, Count data by using a query, Count the
rows in a datasheet, and Display column totals in a datasheet.

Expressions that manipulate and calculate with dates in

calculated fields
Nearly all databases store and track dates and times. You work with dates and times in
Access by setting the date and time fields in your tables to the Date/Time data type. Access
can perform arithmetic calculations on dates; for example, you can calculate how many days
have elapsed since the invoice date to age your accounts receivable.

Expression Description
Creates a field called LagTime, and then uses the
LagTime: DateDiff("d",
DateDiff function to display the number of days
[OrderDate], [ShippedDate])
between the order date and ship date.
Expression Description
Creates a field called YearHired, and then uses the
YearHired:
DatePart function to display the year each employee
DatePart("yyyy",[HireDate])
was hired.
Creates a field called MinusThirty, and then uses the
MinusThirty: Date( )- 30 Date function to display the date 30 days prior to the
current date.

Expressions that count, sum, and average values by using

SQL aggregate or domain aggregate functions
The expressions in the following table use SQL (Structured Query Language) functions that
aggregate or summarize data. You often see these functions (for example, Sum, Count, and
Avg) referred to as aggregate functions.

In addition to aggregate functions, Access also provides "domain" aggregate functions that
you use to sum or count values selectively. For example, you can count only the values
within a certain range or look up a value from another table. The set of domain aggregate
functions includes the DSum Function, the DCount Function, and the DAvg Function.

To calculate totals, you will often need to create a totals query. For example, to summarize by
group, you need to use a Totals query. To enable a Totals query from the query design grid,
click Totals on the View menu.

Expression Description
Creates a field called RowCount, and then uses the
RowCount:Count(*) Count function to count the number of records in the
query, including records with null (blank) fields.
Creates a field called FreightPercentage, and then
calculates the percentage of freight charges in each
subtotal by dividing the sum of the values in the Freight
field by the sum of the values in the Subtotal field.
(This example uses the Sum function.)
FreightPercentage:
Sum([Freight])/Sum([Subtotal])
Note: You must use this expression with a Totals
*100
query. If you set the Format property of the field to
Percent, do not include *100.

For more information about creating a Totals query, see

the article Sum data by using a query.
Creates a field called AverageFreight, and then uses the
AverageFreight: DAvg("[Freight]",
DAvg function to calculate the average freight on all
"[Orders]")
orders combined in a Totals query.

Expressions for working with fields with missing

information (fields with null values)
The expressions shown here work with fields with potentially missing information, such as
those containing null (unknown or undefined) values. You frequently encounter null values,
such as an unknown price for a new product or a value that a coworker forgot to add to an
order. The ability to find and process null values can be a critical part of database operations,
and the expressions in the following table demonstrate some common ways to deal with null
values.

Expression Description
Creates a field called
CurrentCountryRegion, and then
uses the IIf and IsNull functions to
CurrentCountryRegion:IIf(IsNull([CountryRegion]), " display an empty string in that field
", [CountryRegion]) when the CountryRegion field
contains a null value; otherwise, it
displays the contents of the
CountryRegion field.
Creates a field called LeadTime,
and then uses the IIf and IsNull
functions to display the message
LeadTime: IIf(IsNull([RequiredDate] - [ShippedDate]),
"Check for a missing date" if the
"Check for a missing date", [RequiredDate] -
value in either the RequiredDate
[ShippedDate])
field or the ShippedDate field is
null; otherwise, it displays the date
difference.
Creates a field called
SixMonthSales, and then displays
the total of the values in the
SixMonthSales: Nz([Qtr1Sales]) + Nz([Qtr2Sales])
Qtr1Sales and Qtr2Sales fields by
first using the Nz function to
convert any null values to zero.

Expression that uses a subquery to create a calculated

field
You can use a nested query, also called a subquery, to create a calculated field. The
expression in the following table is one example of a calculated field that results from a
subquery.

Expression Description
Creates a field called Cat, and
then displays the
Cat: (SELECT [CategoryName] FROM [Categories] CategoryName, if the
WHERE CategoryID from the
[Products].[CategoryID]=[Categories].[CategoryID]) Categories table is the same as
the CategoryID from the
Products table.
Expressions that define criteria and limit the records in
the result set
You can use an expression to define criteria for a query. Access then returns only those rows
that match the criteria. The steps in this section provide basic information about adding
criteria to a query, and the tables in this section provide examples of criteria for matching text
and date values.

Add criteria to a query

1. In the Navigation Pane, right-click the query that you want to change and click
Design View on the shortcut menu.
2. In the Criteria row of the design grid, click the cell in the column that you want to
use, and then type your criteria.

If you want a larger area in which to type an expression, press SHIFT+F2 to display
the Zoom box.

-or-

On the Design tab, in the Query Setup group, click Builder to start the
Expression Builder and create your expression.

Note: When you create expressions that define criteria, do not precede the expressions with
the = operator.

For more information about using the Expression Builder, see the article Create an
expression.

Expressions that match whole or partial text values

The sample expressions in this table demonstrate criteria that match whole or partial text
values.

Field Expression Description

ShipCity "London" Displays orders shipped to London.
"London" Or "Hedge Uses the Or operator to display orders shipped
ShipCity
End" to London or Hedge End.
Uses the In operator to display orders shipped
ShipCountryRegion In("Canada", "UK")
to Canada or the UK.
Uses the Not operator to display orders
ShipCountryRegion Not "USA"
shipped to countries/regions other than USA.
Uses the Not operator and the * wildcard
ProductName Not Like "C*" character to display products whose names do
not begin with C.
Field Expression Description
Displays orders shipped to companies whose
CompanyName >="N"
names start with the letters N through Z.
Right([ProductCode], Uses the Right function to display orders with
ProductCode
2)="99" ProductCode values that end in 99.
Displays orders shipped to customers whose
ShipName Like "S*"
names start with the letter S.

Expressions that use dates in matching criteria

The expressions in the following table demonstrate the use of dates and related functions in
criteria expressions.

For more information about entering and using date values, see the article Enter a date or
time value. For information about using the functions in these sample expressions, click the
links to the various function topics.

Field Expression Description

Displays orders shipped on
ShippedDate #2/2/2007#
February 2, 2007.
ShippedDate Date() Displays orders shipped today.
Uses the Between...And operator
and the DateAdd and Date
Between Date( ) And DateAdd("m", 3,
RequiredDate functions to display orders required
Date( ))
between today's date and three
months from today's date.
Uses the Date function to display
OrderDate < Date( ) - 30
orders more than 30 days old.
Uses the Year function to display
OrderDate Year([OrderDate])=2007
orders with order dates in 2007.
Uses the DatePart function to
OrderDate DatePart("q", [OrderDate])=4 display orders for the fourth
calendar quarter.
Uses the DateSerial, Year, and
DateSerial(Year ([OrderDate]),
OrderDate Month functions to display orders
Month([OrderDate])+1, 1)-1
for the last day of each month.
Uses the Year and Month
Year([OrderDate])= Year(Now()) And functions and the And operator to
OrderDate
Month([OrderDate])= Month(Now()) display orders for the current year
and month.
Uses the Between...And operator
to display orders shipped no earlier
ShippedDate Between #1/5/2007# And #1/10/2007#
than 5-Jan-2007 and no later than
10-Jan-2007.
Between Date( ) And DateAdd("M", 3, Uses the Between...And operator
RequiredDate
Date( )) to display orders required between
Field Expression Description
today's date and three months from
today's date.
Uses the Month and Date
BirthDate Month([BirthDate])=Month(Date()) functions to display employees who
have birthdays this month.

Expressions that match a missing value (null) or a zero-

length string
The expressions in the following table work with fields that have potentially missing
information — those that might contain a null value or a zero-length string. A null value
represents the absence of information; it does not represent a zero or any value at all. Access
supports this idea of missing information because the concept is vital to the integrity of a
database. In the real world, information is often missing, even if only temporarily (for
example, the as-yet undetermined price for a new product). Therefore, a database that models
a real world entity, such as a business, must be able to record information as missing. You
can use the IsNull function to determine if a field or control contains a null value, and you
can use the Nz function to convert a null value to zero.

Field Expression Description

Displays orders for customers whose ShipRegion field is null
ShipRegion Is Null
(missing).
Displays orders for customers whose ShipRegion field contains a
ShipRegion Is Not Null
value.
Displays orders for customers who don't have a fax machine,
Fax "" indicated by a zero-length string value in the Fax field instead of a
null (missing) value.

Expressions that use patterns to match records

The Like operator provides a great deal of flexibility when you are trying to match rows that
follow a pattern, because you can use Like with wildcard characters and define patterns for
Access to match. For example, the * (asterisk) wildcard character matches a sequence of
characters of any type, and makes it easy to find all names that begin with a letter. For
example, you use the expression Like "S*" to find all names that begin with the letter S.

For more information, see the article Like Operator.

Field Expression Description

ShipName Like "S*" Finds all records in the ShipName field that start with the letter S.
Like Finds all records in the ShipName field that end with the word
ShipName
"*Imports" "Imports".
Finds all records in the ShipName field that begin with the letters
ShipName Like "[A-D]*"
A, B, C, or D.
Field Expression Description
Finds all records in the ShipName field that include the letter
ShipName Like "*ar*"
sequence "ar".
Finds all records in the ShipName field that include "Maison" in
Like "Maison
ShipName the first part of the value and a five-letter string in which the first
Dewe?"
four letters are "Dewe" and the last letter is unknown.
Finds all records in the ShipName field that do not start with the
ShipName Not Like "A*"
letter A.

Expressions that match rows based on the result of a

domain aggregate function
You use a domain aggregate function when you need to sum, count, or average values
selectively. For example, you might want to count only those values that fall within a certain
range, or that evaluate to Yes. At other times, you might need to look up a value from another
table so that you can display it. The sample expressions in the following table use the domain
aggregate functions to perform a calculation on a set of values, and use the result as the query
criteria.

Field Expression Description

Uses the DStDev and DAvg functions to display
> (DStDev("[Freight]",
all orders for which the freight cost rose above
Freight "Orders") + DAvg("[Freight]",
the mean plus the standard deviation for freight
"Orders"))
cost.
Uses the DAvg function to display products
> DAvg("[Quantity]", "[Order
Quantity ordered in quantities above the average order
Details]")
quantity.

Expressions that match based on the results of subqueries

You use a subquery, also called a nested query, to calculate a value for use as a criterion. The
sample expressions in the following table match rows based on the results returned by a
subquery.

Field Expression Displays

(SELECT [UnitPrice] FROM
Products whose price is the same as
UnitPrice [Products] WHERE [ProductName] =
the price of Aniseed Syrup.
"Aniseed Syrup")
>(SELECT AVG([UnitPrice]) FROM Products that have a unit price
UnitPrice
[Products]) above the average.
> ALL (SELECT [Salary] FROM Salary of every sales representative
[Employees] WHERE ([Title] LIKE whose salary is higher than that of
Salary
"*Manager*") OR ([Title] LIKE all employees with "Manager" or
"*Vice President*")) "Vice President" in their titles.
Field Expression Displays
OrderTotal:
> (SELECT AVG([UnitPrice] * Orders with totals that are higher
[UnitPrice] *
[Quantity]) FROM [Order Details]) than the average order value.
[Quantity]

Expressions for use in update queries

You use an update query to modify the data in one or more existing fields in a database. For
example, you can replace values or delete them entirely. This table demonstrates some ways
to use expressions in update queries. You use these expressions in the Update To row in the
query design grid for the field that you want to update.

For more information about creating update queries, see the article Create an update query.

Field Expression Result

Title "Salesperson" Changes a text value to Salesperson.
ProjectStart #8/10/07# Changes a date value to 10-Aug-07.
Retired Yes Changes a No value in a Yes/No field to Yes.
Adds PN to the beginning of each specified
PartNumber "PN" & [PartNumber]
part number.
Calculates the product of UnitPrice and
LineItemTotal [UnitPrice] * [Quantity]
Quantity.
Freight [Freight] * 1.5 Increases freight charges by 50 percent.
DSum("[Quantity] *
Where the ProductID values in the current
[UnitPrice]",
table match the ProductID values in the Order
Sales "Order Details",
Details table, updates sales totals based on the
"[ProductID]=" &
product of Quantity and UnitPrice.
[ProductID])
Truncates the leftmost characters, leaving the
ShipPostalCode Right([ShipPostalCode], 5)
five rightmost characters.
Changes a null (undefined or unknown) value
UnitPrice Nz([UnitPrice])
to a zero (0) in the UnitPrice field.

Expressions used in SQL statements

Structured Query Language, or SQL, is the query language that Access uses. Every query that
you create in query Design view can also be expressed by using SQL. To see the SQL
statement for any query, click SQL View on the View menu. The following table shows
sample SQL statements that employ an expression.

SQL statement that uses an expression Result

SELECT [FirstName],[LastName] FROM Displays the values in the FirstName and
[Employees] WHERE LastName fields for employees whose last
[LastName]="Danseglio" name is Danseglio.
SQL statement that uses an expression Result
Displays the values in the ProductID and
SELECT [ProductID],[ProductName] FROM
ProductName fields in the Products table for
[Products] WHERE
records in which the CategoryID value
[CategoryID]=Forms![New
matches the CategoryID value specified in an
Products]![CategoryID];
open New Products form.
Calculates the average extended price for
SELECT Avg([ExtendedPrice]) AS [Average orders for which the value in the
Extended Price] FROM [Order Details ExtendedPrice field is more than 1000, and
Extended] WHERE [ExtendedPrice]>1000; displays it in a field named Average
Extended Price.
SELECT [CategoryID], Count([ProductID])
In a field named CountOfProductID, displays
AS [CountOfProductID] FROM [Products]
the total number of products for categories
GROUP BY [CategoryID] HAVING
with more than 10 products.
Count([ProductID])>10;

Top of Page

Examples of default value expressions

When you design a database, you might want to assign a default value to a field or control.
Access then supplies the default value when a new record containing the field is created or
when an object that contains the control is created. The expressions in the following table
represent the sample default values for a field or control.

Add a default value for a field in a table

1. In the Navigation Pane, right-click the table that you want to change and click Design
View on the shortcut menu.
2. Click the field that you want to change, and on the General tab, click the Default
Value property box.
3. Type the expression, or click the Build button to the right of the property box to
create an expression by using the Expression Builder.

If a control is bound to a field in a table, and the field has a default value, the default value of
the control takes precedence.

Field Expression Default field value

Quantity 1 1
Region "MT" MT
"New York, New York, N.Y. (Note that you must enclose the value in
Region
N.Y." quotation marks if it includes punctuation.)
A zero-length string to indicate that, by default, this field should
Fax ""
be empty instead of containing a null value
Order
Date( ) Today's date
Date
Field Expression Default field value
DueDate Date() + 60 The date 60 days forward from today

Top of Page

Examples of field validation rule expressions

You can create a validation rule for a field or control by using an expression. Access then
enforces the rule when data is entered into the field or control. To create a validation rule,
you modify the ValidationRule property of the field or control. You should also consider
setting the ValidationText property, which holds the text that Access displays when the
validation rule is violated. If you don't set the ValidationText property, Access displays a
default error message.

Add a validation rule to a field

1. In the Navigation Pane, right-click the table that you want to change and click Design
View on the shortcut menu.
2. Click the field that you want to change.
3. Click the Validation Rule property box, located in the lower section of the table
designer.
4. Type the expression, or click the Build button to the right of the property box to
create an expression by using the Expression Builder.

Note: Do not precede the expression with the = operator when you create a validation
rule.

The examples in the following table demonstrate the validation rule expressions for the
ValidationRule property and the associated text for the ValidationText property.

ValidationRule property ValidationText property

<> 0 Please enter a nonzero value.
0 Or > 100 Value must be either 0 or more than 100.
Value must be four characters, beginning with the letter
Like "K???"
K.
< #1/1/2007# Enter a date prior to 1/1/2007.
>= #1/1/2007# And <
Date must occur in 2007.
#1/1/2008#

For more information about validating data, see the article Create a validation rule to validate
data in a field.

Top of Page

Examples of macro condition expressions

In some cases, you might want to carry out an action or series of actions in a macro only if a
particular condition is true. For example, suppose you want an action to run only when the
value of the Counter text box is 10. You use an expression to define the condition in the
Condition column of the macro: [Counter]=10.

Add a condition for a macro action

1. In the Navigation Pane, right-click the macro that you want to change and click
Design View on the shortcut menu.
2. If you don't see the Condition column in the macro designer, on the Design tab, in
the Show/Hide group, click Conditions.
3. Click the Condition cell for the macro action that you want to change, and then type
your conditional expression.
4. Save your changes and close the macro.

As with the ValidationRule property, the Condition column expression is a conditional

expression. It must resolve to either a True or False value. The action takes place only when
the condition is true.

Use this expression to carry out the

If
action
Paris is the City value in the field on the form
[City]="Paris"
from which the macro was run.
There are more than 35 entries in the OrderID
DCount("[OrderID]", "Orders") > 35
field of the Orders table.
There are more than three entries in the Order
DCount("*", "[Order Details]",
Details table for which the OrderID field of the
"[OrderID]=" &
table matches the OrderID field on the Orders
Forms![Orders]![OrderID]) > 3
form.
The value of the ShippedDate field on the form
[ShippedDate] Between #2-Feb-2007#
from which the macro is run is no earlier than 2-
And #2-Mar-2007#
Feb-2007 and no later than 2-Mar-2007.
The value of the UnitsInStock field on the
Forms![Products]![UnitsInStock] < 5
Products form is less than 5.
The FirstName value on the form from which
IsNull([FirstName]) the macro is run is null (has no value). This
expression is equivalent to [FirstName] Is Null.
The value in the CountryRegion field on the
[CountryRegion]="UK" And form from which the macro is run is UK, and
Forms![SalesTotals]![TotalOrds] > 100 the value of the TotalOrds field on the
SalesTotals form is greater than 100.
The value in the CountryRegion field on the
[CountryRegion] In ("France", "Italy", form from which the macro is run is either
"Spain") And Len([PostalCode])<>5 France, Italy, or Spain, and the postal code is
not 5 characters long.
You click OK in a dialog box that the MsgBox
MsgBox("Confirm changes?",1)=1 function displays. If you click Cancel in the
dialog box, Access ignores the action.
Note: To force Access to temporarily ignore an action, type False as a condition. Forcing
Access to temporarily ignore an action can be helpful when you are trying to find problems in
a macro.