Examples of using expressions with strings in Access

Applies to
Microsoft Office Access 2003
Microsoft Access 2000 and 2002

An expression is a combination of identifiers, operators, and values that produces a result. You can use expressions as settings for many properties and arguments; to define calculated controls in forms, reports, and (in Access 2002 and later) data access pages; to set criteria or define calculated fields in queries; and to set conditions in macros.

 Tip    Try Office 2010 In Access 2010, the Expression Builder has IntelliSense, so you can see what arguments your expression requires.
Watch a video or try Office 2010.

You can build expressions manually or with the Expression Builder, which is available from most places where you write expressions, such as in a property sheet, a Criteria cell in the query design grid, or the Macro window.

Using expressions in calculated controls

Expression Description
=[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.

Using expressions with page numbers

The examples in the following table assume you are working with page 1 of a 3 page report.

Expression 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] & " - " & [Page] UK - 1
=Format([Page], "000") 001

Using expressions to perform arithmetic operations

If you use this expression Access displays
=[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.

Using expressions in aggregate functions

Expression Description
=Avg([Freight]) Uses the Avg function to display the average of the values of the Freight control.
=Count([OrderID]) Uses the Count function to display the number of records in the OrderID control.
=Sum([Sales]) Uses the Sum function to display the sum of the values of the Sales control.
=Sum([Quantity]*[Price]) Uses the Sum function to display the sum of the product of the values of the Quantity and Price controls.
=[Sales]/Sum([Sales])*100 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.

Using expressions to return one of two values

Expression Description
=IIf([Confirmed] = "Yes", "Order Confirmed", "Order Not Confirmed") Uses the IIf function to display 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]) Uses the IIf function to display an empty string if the value of the Country field is Null; otherwise, it displays the value of the Country control.
=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.
 
 
Applies to:
Access 2003