Examples of expressions

Microsoft Office Access 2003

previous page next page

ShowExamples of manipulating and calculating dates

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.

ShowExamples of manipulating text values

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.

ShowExamples of manipulating arithmetic operations

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])

ShowExamples of conditional expressions

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.

ShowExamples of aggregate and domain aggregate functions

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).

ShowExamples of expressions that use 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.

Note  If the control's Format property is set to Percent, don't include the *100.

ShowExamples of expressions that use domain aggregate functions

Expression Description
=DLookup("[ContactName]", "[Suppliers]", "[SupplierID] = Forms![SupplierID]") Uses the DLookup function to display the value of the ContactName field in the Suppliers table where the value of the SupplierID field in the table matches the value of the SupplierID control on the active form.
=DLookup("[ContactName]", "[Suppliers]", "[SupplierID] = Forms![New Suppliers]![SupplierID]") Uses the DLookup function to display the value of the ContactName field in the Suppliers table where the value of the SupplierID field in the table matches the value of the SupplierID control on the New Suppliers form.
=DSum("[OrderAmount]", "[Orders]", "[CustomerID] = 'RATTC'") Uses the DSum function to display the sum total of values of the OrderAmount field in the Orders table where the CustomerID is RATTC.

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.

ShowExamples of expressions used in queries and filters

ShowExamples of criteria used to retrieve records

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

Expression Result
> 234 For a Quantity field, numbers greater than 234
< 1200.45 For a UnitPrice field, numbers less than 1200.45
>= "Callahan" For a LastName field, all names from Callahan through the end of the alphabet
Between #2/2/1999# And #12/1/1999# For an OrderDate field, dates from 2-Feb-99 through 1-Dec-99 (ANSI-89)
Between '2/2/1999' And '12/1/1999' For an OrderDate field, dates from 2-Feb-99 through 1-Dec-99 (ANSI-92)

ShowValues that don't match (Not)

Expression Result
Not "USA" For a ShipCountry field, orders shipped to countries other than the USA
Not 2 For an ID field, the employee whose ID doesn't equal 2
Not T* For a LastName field, employees whose names don't start with the letter "T" (ANSI-89)
Not T% For a LastName field, employees whose names don't start with the letter "T" (ANSI-92)

ShowValues in a list (In)

Expression Result
In("Canada", "UK") For a ShipCountry field, orders shipped to Canada or the UK
In(France, Germany, Japan) For a CountryName field, employees living in France or Germany or Japan

ShowText, partial, and matching values

Expression Result
"London" For a ShipCity field, orders shipped to London
"London" Or "Hedge End" For a ShipCity field, orders shipped to London or Hedge End
>="N" For a CompanyName field, orders shipped to companies whose name starts with the letters N through Z
Like "S*" For a ShipName field, orders shipped to customers whose name starts with the letter S (ANSI-89)
Like "S%" For a ShipName field, orders shipped to customers whose name starts with the letter S (ANSI-92)
Right([OrderID], 2) = "99" For an OrderID field, orders with ID values ending in 99
Len([CompanyName]) > Val(30) For a CompanyName field, orders for companies whose name is more than 30 characters long

ShowPart of a field's value (Like)

Expression Result
Like "S*" For a ShipName field, orders shipped to customers whose names start with the letter S (ANSI-89)
Like "S%" For a ShipName field, orders shipped to customers whose names start with the letter S (ANSI-92)
Like "*Imports" For a ShipName field, orders shipped to customers whose names end with the word "Imports" (ANSI-89)
Like "%Imports" For a ShipName field, orders shipped to customers whose names end with the word "Imports" (ANSI-92)
Like "[A-D]*" For a ShipName field, orders shipped to customers whose names start with A through D (ANSI-89)
Like "[A-D]%" For a ShipName field, orders shipped to customers whose names start with A through D (ANSI-92)
Like "*ar*" For a ShipName field, orders shipped to customers whose names include the letter sequence "ar" (ANSI-89)
Like "%ar%" For a ShipName field, orders shipped to customers whose names include the letter sequence "ar" (ANSI-92)
Like "Maison Dewe?" For a ShipName field, orders shipped to the customer with "Maison" as the first part of its name and a 5-letter second name in which the first 4 letters are "Dewe" and the last letter is unknown (ANSI-89)
Like "Maison Dewe_" For a ShipName field, orders shipped to the customer with "Maison" as the first part of its name and a 5-letter second name in which the first 4 letters are "Dewe" and the last letter is unknown (ANSI-92)

ShowDates

Expression Result
#2/2/2000# For a ShippedDate field, orders shipped on February 2, 2000 (ANSI-89)
'2/2/2000' For a ShippedDate field, orders shipped on February 2, 2000 (ANSI-92)
Date() For a RequiredDate field, orders for today's date
Between Date( ) And DateAdd("M", 3, Date( )) For a RequiredDate field, orders required between today's date and three months from today's date
< Date( ) - 30 For an OrderDate field, orders more than 30 days old
Year([OrderDate]) = 1999 For an OrderDate field, orders with order dates in 1999
DatePart("q", [OrderDate]) = 4 For an OrderDate field, orders for the fourth calendar quarter
DateSerial(Year ([OrderDate]), Month([OrderDate]) + 1, 1) - 1 For an OrderDate field, orders for the last day of each month
Year([OrderDate]) = Year(Now()) And Month([OrderDate]) = Month(Now()) For an OrderDate field, orders for the current year and month

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

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

ShowThe result of a domain aggregate function

Expression Result
> (DStDev("[Freight]", "Orders") + DAvg("[Freight]", "Orders")) For a Freight field, orders for which the freight cost rose above the mean plus the standard deviation for freight cost
>DAvg("[Quantity]", "Order Details") For a Quantity field, products ordered in quantities above the average order quantity

ShowThe result of a subquery as criteria

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

ShowExamples of expressions used in calculated fields

ShowManipulate text values

Expression Result
FullName: [FirstName] & " " & [LastName] Displays in the FullName field the value of the FirstName and LastName fields, separated by a space
Address2: [City] & " " & [Region] & " " & [PostalCode] Displays in the Address2 field the value of the City, Region, and PostalCode fields, separated by spaces
ProductInitial: Left([ProductName], 1) Displays in the ProductInitial field the first character of the value in the ProductName field
TypeCode: Right([AssetCode], 2) Displays in the TypeCode field the last two characters of the value in the AssetCode field
AreaCode: Mid([Phone], 2, 3) Displays in the AreaCode field the three characters starting with the second character of the value in the Phone field

ShowPerform arithmetic operations

Expression Result
PrimeFreight: [Freight] * 1.1 Displays in the PrimeFreight field freight charges plus 10 percent
OrderAmount: [Quantity] * [UnitPrice] Displays in the OrderAmount field the product of the values in the Quantity and UnitPrice fields
LeadTime: [RequiredDate] - [ShippedDate] Displays in the LeadTime field the difference between the values in the RequiredDate and ShippedDate fields
TotalStock: [UnitsInStock] + [UnitsOnOrder] Displays in the TotalStock field the sum of the values in the UnitsInStock and UnitsOnOrder fields
FreightPercentage: Sum([Freight]) / Sum([Subtotal]) * 100 Displays in the FreightPercentage field 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.

The Total row in the design grid must be displayed, and the Total cell for this field must be set to Expression.

If the Format property of the field is set to Percent, don't include the *100.
SumofUnits: Nz([UnitsInStock], 0) + Nz([UnitsOnOrder], 0) Displays in the SumofUnits field the sum of all units in stock and on order.

When you use an arithmetic operator (+, -, *, /) in an expression and the value of one of the fields in the expression is Null, the result of the entire expression will be Null. If some records in one of the fields you used in the expression might have a Null value, you can convert the Null value to zero using the Nz function.

ShowManipulate and calculate dates

Expression Result
LagTime: DateDiff("d", [OrderDate], [ShippedDate]) Display in the LagTime field the number of days between the order date and ship date
YearHired: DatePart("yyyy", [HireDate]) Displays in the YearHired field the year each employee was hired
MonthNo: DatePart("M", [OrderDate]) Displays in the MonthNo field the number of the month
PriorDate: Date( ) - 30 Displays in the PriorDate field the date 30 days prior to the current date

ShowUse SQL and domain aggregate functions

Expression Result
Count(*) Uses the Count function to count the number of records in the query, including records with Null (blank) fields
FreightPercentage: Sum([Freight]) / Sum([Subtotal]) * 100 Displays in the FreightPercentage field 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.

The Total row in the design grid must be displayed, and the Total cell for this field must be set to Expression.

If the Format property of the field is set to Percent, don't include the *100.
AverageFreight: DAvg("[Freight]", "[Orders]") Displays in the AverageFreight field the average discount given on all orders combined in a totals query

ShowWork with Null values

Expression Result
CurrentCountry: IIf(IsNull([Country]), " ", [Country]) Displays in the CurrentCountry field an empty string if the Country field is Null; otherwise, it displays the contents of the Country field
LeadTime: IIf(IsNull([RequiredDate] - [ShippedDate]), "Check for a missing date", [RequiredDate] - [ShippedDate]) Displays in the LeadTime field the message "Check for a missing date" if the value of either the RequiredDate or ShippedDate fields is Null; otherwise, it displays the difference
SixMonthSales: Nz([Qtr1Sales], 0) + Nz([Qtr2Sales], 0) Displays in the SixMonthSales field the total of the values in the first-quarter and second-quarter sales fields combined, using the Nz function to convert the Null values to zero first

ShowUse a subquery

Expression Result
Category: (SELECT [CategoryName] FROM [Categories] WHERE [Products].[CategoryID] = [Categories].[CategoryID]) Displays in the Category field the CategoryName if the CategoryID from the Categories table is the same as the CategoryID from the Products table

ShowExamples of expressions used in update queries

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

ShowExamples of expressions used in SQL statements

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.

ShowExamples of default value expressions

Expression Default field value
1 1
"MT" MT
"New York, N.Y." New York, N.Y. (note that you must enclose the value in quotes if it includes punctuation)
" " Zero-length string
Date( ) Today's date
=Yes Yes displayed in the local language of the computer.

ShowExamples of macro condition expressions

You can use any expression that evaluates to True/False or Yes/No in a macro condition. The macro will be executed if the condition evaluates to True (or Yes).

ShowTip

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.

Use this expression To carry out the action if
[City]="Paris" Paris is the City value in the field on the form from which the macro was run.
DCount("[OrderID]", "Orders")>35 There are more than 35 entries in the OrderID field of the Orders table.
DCount("*", "Order Details", "[OrderID]=Forms![Orders]![OrderID]")>3 There are more than three entries in the Order Details table for which the OrderID field of the table matches the OrderID field on the Orders form.
[ShippedDate] Between #2-Feb-2001# And #2-Mar-2001# The value of the ShippedDate field on the form from which the macro is run is no earlier than 2-Feb-2001 and no later than 2-Mar-2001.
Forms![Products]![UnitsInStock]<5 The value of the UnitsInStock field on the Products form is less than five.
IsNull([FirstName]) The FirstName value on the form from which the macro is run is Null (has no value). This expression is equivalent to [FirstName] Is Null.
[Country]="UK" And Forms![SalesTotals]![TotalOrds]>100 The value in the Country field on the form from which the macro is run is UK, and the value of the TotalOrds field on the SalesTotals form is greater than 100.
[Country] In ("France", "Italy", "Spain") And Len([PostalCode])<>5 The value in the Country field on the form from which the macro is run is France, Italy, or Spain, and the postal code isn't five characters long.
MsgBox("Confirm changes?",1)=1 You click OK in a dialog box in which the MsgBox function displays "Confirm changes?". If you click Cancel in the dialog box, Microsoft Access ignores the action.
previous page start next page