Examples of expressions

The following table lists examples of expressions that you can use in calculated controls on forms, reports, and data access pages.

Expression	Description
=Date()	Uses the Date function to display the current date in the form of mm-dd-yyyy, where mm is the month (1 through 12), dd is the day (1 through 31), and yyyy is the year (1980 through 2099).
=Format(Now(), "ww")	Uses the Format function to display the number of the week of the year the current date represents, where ww is 1 through 53.
=DatePart("yyyy", [OrderDate])	Uses the DatePart function to display the four-digit year of the value of the OrderDate field.
=DateAdd("y", -10, [PromisedDate])	Uses the DateAdd function to display a date that is 10 days before the value of the PromisedDate field.
=DateDiff("d", [OrderDate], [ShippedDate])	Uses the DateDiff function to display the variance in days between the values of the OrderDate and ShippedDate fields.

Notes

• In the ControlSource property of a calculated control, precede the expression with the = operator. On a data access page, you can omit the = operator, and type an alias instead; for example, type WeekNumber: Format(Now(), "ww").
• When you set the Name property of a calculated control in a form or report, or set the ID property of a calculated control in a data access page, make sure that you use a unique name. Don't use the name or ID of one of the controls you used in the expression.
• In an expression on a form or report, you can use the name of a control or the name of a field in the underlying records source. In an expression on a data access page, you can use only the name of a field that's in the data definition of the page.

The following table lists examples of expressions that you can use in calculated controls on forms, reports, and data access pages.

Expression	Description
="N/A"	Displays N/A.
=[FirstName]&" "&[LastName]	Displays the values of the FirstName and LastName fields separated by a space.
=Left([ProductName], 1)	Uses the Left function to display the first character of the value of the ProductName field.
=Right([AssetCode], 2)	Uses the Right function to display the last 2 characters of the value of the AssetCode field.
=Trim([Address])	Uses the Trim function to display the value of the Address field, removing any leading or trailing spaces.
=IIf(IsNull([Region]),[City]&" "& [PostalCode],[City]&" "&[Region]&" "& [PostalCode])	Uses the IIf function to display the values of the City and PostalCode fields if Region is Null; otherwise, it displays the values of the City, Region, and PostalCode fields, separated by spaces.

Notes

• In the ControlSource property of a calculated control, precede the expression with the = operator. On a data access page, you can omit the = operator, and type an alias instead; for example, type FullName: [FirstName]&" "& [LastName].
• When you set the Name property of a calculated control in a form or report, or set the ID property of a calculated control in a data access page, make sure to use a unique name. Don't use the name or ID of one of the controls that you used in the expression.
• In an expression on a form or report, you can use the name of a control or field in the underlying record source. In an expression on a data access page, you can use only the name of a field that's in the data definition of that page.

The following table lists examples of expressions that you can use in calculated controls on forms, reports, and data access pages.

Expression	Description
=[Subtotal] + [Freight]	The sum of the values of the Subtotal and Freight fields.
=[RequiredDate] - [ShippedDate]	The difference between the values of the RequiredDate and ShippedDate fields.
=[Price] * 1.06	The product of the value of the Price field and 1.06 (adds 6 percent to the Price value).
=[Quantity] * [Price]	The product of the values of the Quantity and Price fields.
=[EmployeeTotal] / [CountryTotal]	The quotient of the values of the EmployeeTotal and CountryTotal fields.

Notes

• In the ControlSource property of a calculated control, precede the expression with the = operator. On a data access page, you can omit the = operator, and type an alias instead; for example, type ExtendedPrice: [Quantity] * [Price].
• When you set the Name property of a calculated control in a form or report, or set the ID property of a calculated control on a data access page, make sure to use a unique name. Don't use the name or ID of one of the controls that you used in the expression.
• In an expression on a form or report, you can use the name of a control or the name of a field in the underlying record source. In an expression on a data access page, you can use only the name of a field that's in the data definition of the page.
• When you use an arithmetic operator (+, -, *, /) 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. On a form or report, if some records in one of the controls that you used in the expression might have a Null value, you can convert the Null value to zero by using the Nz function; for example:

  =Msodsc.Nz([Subtotal]) + Msodsc.Nz([Freight])
  								

The following table lists examples of expressions that you can use in calculated controls on forms, reports, and data access pages.

Expression	Description
=IIf([Confirmed] = "Yes", "Order Confirmed", "Order Not Confirmed")	Displays the message "Order Confirmed" if the value of the Confirmed field is Yes; otherwise, it displays the message "Order Not Confirmed."
=IIf(IsNull([Country])," ", [Country])	Displays an empty string if the value of the Country field is Null; otherwise, it displays the value of the Country field.
=IIf(IsNull([Region]),[City]&" "& [PostalCode], [City]&" "&[Region]&" " &[PostalCode])	Display the values of the City and PostalCode fields if Region is Null; otherwise, it displays the values of the City, Region, and PostalCode fields.
=IIf(IsNull([RequiredDate] - [ShippedDate]), "Check for a missing date", [RequiredDate] - [ShippedDate])	Display the message "Check for a missing date" if the result of subtracting ShippedDate from RequiredDate is Null; otherwise, it displays the difference between the values of the RequiredDate and ShippedDate fields.

Notes

• In the ControlSource property of a calculated control, precede the expression with the = operator. On a data access page, you can omit the = operator, and type an alias instead; for example, type DisplayCountry: IIf(IsNull([Country])," ",[Country]).
• When you set the Name property of a calculated control in a form or report, or set the ID property of a calculated control in a data access page, make sure to use a unique name. Don't use the name or ID of one of the controls that you used in the expression.
• In an expression on a form or report, you can use the name of a control or the name of a field in the underlying record source. In an expression on a data access page, you can use only the name of a field that's in the data definition of the page.

Note  You can't use aggregate and domain aggregate functions in a data access page. Additionally, you can't use domain aggregate functions in a Microsoft Access project (.adp).

Examples of expressions that use aggregate functions

Examples of expressions that use domain aggregate functions

Notes

• In a calculated control, precede the expression with the = operator.
• When you set the Name property of a calculated control, make sure you use a unique name. Don't use the name of one of the controls you used in the expression.

Ranges of values (>, <, >=, <=, <>, or Between...And)

Values that don't match (Not)

Values in a list (In)

Text, partial, and matching values

Part of a field's value (Like)

Dates

A blank field's value (Null or zero-length string)

The result of a domain aggregate function

The result of a subquery as criteria

Manipulate text values

Perform arithmetic operations

Manipulate and calculate dates

Use SQL and domain aggregate functions

Work with Null values

Use a subquery

Use expressions such as the following in the Update To cell in the query design grid for the field you want to update.

Expression	Result
"Salesperson"	Changes a text value to Salesperson
#8/10/99#	Changes a date value to 10-Aug-99
Yes	Changes a No value in a Yes/No field to Yes
"PN" & [PartNumber]	Adds PN to the beginning of each specified part number
[UnitPrice] * [Quantity]	Calculates the product of UnitPrice and Quantity
[Freight] * 1.5	Increases freight charges by 50 percent
DSum("[Quantity] * [UnitPrice]", "Order Details", "[ProductID]=" & [ProductID])	Where the Product IDs in the current table match the Product IDs in the Order Details table, updates sales totals based on the product of Quantity and UnitPrice
Right([ShipPostalCode], 5)	Truncates the leftmost characters, leaving the five rightmost characters
IIf(IsNull([UnitPrice]), 0, [UnitPrice])	Changes a Null value to a zero (0) in the UnitPrice field

You can use an expression in many places in an SQL statement, as the following examples show. Expressions are shown in bold text.

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

To cause Microsoft Access to temporarily ignore an action, enter False as a condition. Temporarily ignoring an action can be helpful when you are trying to find problems in a macro.