Examples of expressions

="N/A"

Displays N/A.

=[FirstName] & " " & [LastName]

Displays the values that reside in table fields called FirstName and LastName. In this example, the & operator is used to combine the FirstName field, a space character (enclosed in quotation marks), and the LastName field.

=Left([ProductName], 1)

Uses the Left function to display the first character of the value of a field or control called ProductName.

=Right([AssetCode], 2)

Uses the Right function to display the last 2 characters of the value in a field or control called AssetCode.

=Trim([Address])

Uses the Trim function to display the value of the Address control, 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 controls if the value in the Region control is null; otherwise, it displays the values of the City, Region, and PostalCode controls, separated by spaces.

=[City] & (" " + [Region]) & " " & [PostalCode]

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 fields or controls, separated by spaces.

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.

=[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/17

=[Subtotal]+[Freight]

The sum of the values of the Subtotal and Freight fields or controls.

=[RequiredDate]-[ShippedDate]

The interval between the date values of the RequiredDate and ShippedDate fields or controls.

=[Price]*1.06

The product of the value of the Price field or control and 1.06 (adds 6 percent to the Price value).

=[Quantity]*[Price]

The product of the values of the Quantity and Price fields or controls.

=[EmployeeTotal]/[CountryRegionTotal]

The quotient of the values of the EmployeeTotal and CountryRegionTotal fields or controls.

=Forms![Orders]![OrderID]

The value of the OrderID control on the Orders form.

=Forms![Orders]![Orders Subform].Form![OrderSubtotal]

The value of the OrderSubtotal control on the subform named Orders Subform on the Orders form.

=Forms![Orders]![Orders Subform]![ProductID].Column(2)

The value of the third column in ProductID, a multiple-column list box on the subform named Orders Subform on the Orders form. (Note that 0 refers to the first column, 1 refers to the second column, and so on.)

=Forms![Orders]![Orders Subform]![Price] * 1.06

The product of the value of the Price control on the subform named Orders Subform on the Orders form and 1.06 (adds 6 percent to the value of the Price control).

=Parent![OrderID]

The value of the OrderID control on the main or parent form of the current subform.

=Report![Invoice]![OrderID]

The value of a control called "OrderID" in a report called "Invoice."

=Report![Summary]![Summary Subreport]![SalesTotal]

The value of the SalesTotal control on the subreport named Summary Subreport on the Summary report.

=Parent![OrderID]

The value of the OrderID control on the main or parent report of the current subreport.

=Avg([Freight])

Uses the Avg function to display the average of the values of a table field or control named "Freight."

=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 the 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.

If you set the Format property of the control to Percent, do not include *100 in the expression.

=DLookup("[ContactName]", "[Suppliers]", "[SupplierID] = " & Forms("Suppliers")("[SupplierID]"))

Uses the DLookup function to return 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 Suppliers form.

=DLookup("[ContactName]", "[Suppliers]", "[SupplierID] = " & Forms![New Suppliers]![SupplierID])

Uses the DLookup function to return 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 return the sum total of the values in the OrderAmount field in the Orders table where the CustomerID is RATTC.

=DCount("[Retired]","[Assets]","[Retired]=Yes")

Uses the DCount function to return the number of Yes values in the Retired field (a Yes/No field) in the Assets table.

=Date()

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 (1 through 31), and yy is the last two digits of the year (1980 through 2099).

=Format(Now(), "ww")

Uses the Format function to display the week number of the year for the current date, where ww represents weeks 1 through 53.

=DatePart("yyyy", [OrderDate])

Uses the DatePart function to display the four-digit year of the value of the OrderDate control.

=DateAdd("y", -10, [PromisedDate])

Uses the DateAdd function to display a date that is 10 days before the value of the PromisedDate control.

=DateDiff("d", [OrderDate], [ShippedDate])

Uses the DateDiff function to display the number of days' difference between the values of the OrderDate and ShippedDate controls.

=[InvoiceDate] + 30

Uses arithmetic operations on dates to calculate the date 30 days after the date in the InvoiceDate field or control.

=IIf([Confirmed] = "Yes", "Order Confirmed", "Order Not Confirmed")

Uses the IIf (Immediate If) function to display the message "Order Confirmed" if the value of the Confirmed control is Yes; otherwise, it displays the message "Order Not Confirmed."

=IIf(IsNull([Country/region]), " ", [Country])

Uses the IIf and IsNull functions to display an empty string if the value of the Country/region control is null; otherwise, it displays the value of the Country/region control.

=IIf(IsNull([Region]), [City] & " " & [PostalCode], [City] & " " & [Region] & " " & [PostalCode])

Uses the IIf and IsNull functions to display the values of the City and PostalCode controls if the value in the Region control is null; otherwise, it displays the values of the City, Region, and PostalCode fields or controls.

=IIf(IsNull([RequiredDate]) Or IsNull([ShippedDate]), "Check for a missing date", [RequiredDate] - [ShippedDate])

Uses the IIf and IsNull functions to display the message "Check for a missing date" if the result of subtracting ShippedDate from RequiredDate is null; otherwise, it displays the interval between the date values of the RequiredDate and ShippedDate controls.

FullName: [FirstName] & " " & [LastName]

Creates a field called FullName that displays the values in the FirstName and LastName fields, separated by a space.

Address2: [City] & " " & [Region] & " " & [PostalCode]

Creates a field called Address2 that displays the values in the City, Region, and PostalCode fields, separated by spaces.

ProductInitial: Left([ProductName], 1)

Creates a field called ProductInitial, and then uses the Left function to display, in the ProductInitial field, the first character of the value in the ProductName field.

TypeCode: Right([AssetCode], 2)

Creates a field called TypeCode, and then uses the Right function to display the last two characters of the values in the AssetCode field.

AreaCode: Mid([Phone],2,3)

Creates a field called AreaCode, and then uses the Mid function to display the three characters starting with the second character of the value in the Phone field.

ExtendedPrice: CCur([Order Details].[Unit Price]*[Quantity]*(1-[Discount])/100)*100

Names the calculated field ExtendedPrice, and uses the CCur function to calculate the line item totals with an applied discount.

PrimeFreight: [Freight] * 1.1

Creates a field called PrimeFreight, and then displays freight charges plus 10 percent in the field.

OrderAmount: [Quantity] * [UnitPrice]

Creates a field called OrderAmount, and then displays the product of the values in the Quantity and UnitPrice fields.

LeadTime: [RequiredDate] - [ShippedDate]

Creates a field called LeadTime, and then displays the difference between the values in the RequiredDate and ShippedDate fields.

TotalStock: [UnitsInStock]+[UnitsOnOrder]

Creates a field called TotalStock, and then displays the sum of the values in the UnitsInStock and UnitsOnOrder fields.

FreightPercentage: Sum([Freight])/Sum([Subtotal]) *100

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 query into a Totals query because you need to use the Total row in the design grid, and you must set the 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.

LagTime: DateDiff("d", [OrderDate], [ShippedDate])

Creates a field called LagTime, and then uses the DateDiff function to display the number of days between the order date and ship date.

YearHired: DatePart("yyyy",[HireDate])

Creates a field called YearHired, and then uses the DatePart function to display the year each employee was hired.

MinusThirty: Date( )- 30

Creates a field called MinusThirty, and then uses the Date function to display the date 30 days prior to the current date.

RowCount: Count(*)

Creates a field called RowCount, and then 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

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

You must use this expression with a Totals 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.

AverageFreight: DAvg("[Freight]", "[Orders]")

Creates a field called AverageFreight, and then uses the DAvg function to calculate the average freight on all orders combined in a Totals query.

CurrentCountryRegion: IIf(IsNull([CountryRegion]), " ", [CountryRegion])

Creates a field called CurrentCountryRegion, and then uses the IIf and IsNull functions to display an empty string in that field when the CountryRegion field contains a null value; otherwise, it displays the contents of the CountryRegion field.

LeadTime: IIf(IsNull([RequiredDate] - [ShippedDate]), "Check for a missing date", [RequiredDate] - [ShippedDate])

Creates a field called LeadTime, and then uses the IIf and IsNull functions to display the message "Check for a missing date" if the value in either the RequiredDate field or the ShippedDate field is null; otherwise, it displays the date difference.

SixMonthSales: Nz([Qtr1Sales]) + Nz([Qtr2Sales])

Creates a field called SixMonthSales, and then displays the total of the values in the Qtr1Sales and Qtr2Sales fields by first using the Nz function to convert any null values to zero.

Cat: (SELECT [CategoryName] FROM [Categories] WHERE [Products].[CategoryID]=[Categories].[CategoryID])

Creates a field called Cat, and then displays the CategoryName, if the CategoryID from the Categories table is the same as the CategoryID from the Products table.

ShipCity

"London"

Displays orders shipped to London.

ShipCity

"London" Or "Hedge End"

Uses the Or operator to display orders shipped to London or Hedge End.

ShipCountryRegion

In("Canada", "UK")

Uses the In operator to display orders shipped to Canada or the UK.

ShipCountryRegion

Not "USA"

Uses the Not operator to display orders shipped to countries/regions other than USA.

ProductName

Not Like "C*"

Uses the Not operator and the * wildcard character to display products whose names do not begin with C.

CompanyName

>="N"

Displays orders shipped to companies whose names start with the letters N through Z.

ProductCode

Right([ProductCode], 2)="99"

Uses the Right function to display orders with ProductCode values that end in 99.

ShipName

Like "S*"

Displays orders shipped to customers whose names start with the letter S.

ShippedDate

#2/2/2017#

Displays orders shipped on February 2, 2017.

ShippedDate

Date()

Displays orders shipped today.

RequiredDate

Between Date( ) And DateAdd("m", 3, Date( ))

Uses the Between...And operator and the DateAdd and Date functions to display orders required between today's date and three months from today's date.

OrderDate

< Date( ) - 30

Uses the Date function to display orders more than 30 days old.

OrderDate

Year([OrderDate])=2017

Uses the Year function to display orders with order dates in 2017.

OrderDate

DatePart("q", [OrderDate])=4

Uses the DatePart function to display orders for the fourth calendar quarter.

OrderDate

DateSerial(Year ([OrderDate]), Month([OrderDate])+1, 1)-1

Uses the DateSerial, Year, and Month functions to display orders for the last day of each month.

OrderDate

Year([OrderDate])= Year(Now()) And Month([OrderDate])= Month(Now())

Uses the Year and Month functions and the And operator to display orders for the current year and month.

ShippedDate

Between #1/5/2017# And #1/10/2017#

Uses the Between...And operator to display orders shipped no earlier than 5-Jan-2017 and no later than 10-Jan-2017.

RequiredDate

Between Date( ) And DateAdd("M", 3, Date( ))

Uses the Between...And operator to display orders required between today's date and three months from today's date.

BirthDate

Month([BirthDate])=Month(Date())

Uses the Month and Date functions to display employees who have birthdays this month.

ShipRegion

Is Null

Displays orders for customers whose ShipRegion field is null (missing).

ShipRegion

Is Not Null

Displays orders for customers whose ShipRegion field contains a value.

Fax

""

Displays 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 (missing) value.

ShipName

Like "S*"

Finds all records in the ShipName field that start with the letter S.

ShipName

Like "*Imports"

Finds all records in the ShipName field that end with the word "Imports".

ShipName

Like "[A-D]*"

Finds all records in the ShipName field that begin with the letters A, B, C, or D.

ShipName

Like "*ar*"

Finds all records in the ShipName field that include the letter sequence "ar".

ShipName

Like "Maison Dewe?"

Finds all records in the ShipName field that include "Maison" in the first part of the value and a five-letter string in which the first four letters are "Dewe" and the last letter is unknown.

ShipName

Not Like "A*"

Finds all records in the ShipName field that do not start with the letter A.

UnitPrice

(SELECT [UnitPrice] FROM [Products] WHERE [ProductName] = "Aniseed Syrup")

Products whose price is the same as the price of Aniseed Syrup.

UnitPrice

>(SELECT AVG([UnitPrice]) FROM [Products])

Products that have a unit price above the average.

Salary

> ALL (SELECT [Salary] FROM [Employees] WHERE ([Title] LIKE "*Manager*") OR ([Title] LIKE "*Vice President*"))

Salary of every sales representative whose salary is higher than that of all employees with "Manager" or "Vice President" in their titles.

OrderTotal: [UnitPrice] * [Quantity]

> (SELECT AVG([UnitPrice] * [Quantity]) FROM [Order Details])

Orders with totals that are higher than the average order value.

Title

"Salesperson"

Changes a text value to Salesperson.

ProjectStart

#8/10/17#

Changes a date value to 10-Aug-17.

Retired

Yes

Changes a No value in a Yes/No field to Yes.

PartNumber

"PN" & [PartNumber]

Adds PN to the beginning of each specified part number.

LineItemTotal

[UnitPrice] * [Quantity]

Calculates the product of UnitPrice and Quantity.

Freight

[Freight] * 1.5

Increases freight charges by 50 percent.

Sales

DSum("[Quantity] * [UnitPrice]", "Order Details", "[ProductID]=" & [ProductID])

Where the ProductID values in the current table match the ProductID values in the Order Details table, updates sales totals based on the product of Quantity and UnitPrice.

ShipPostalCode

Right([ShipPostalCode], 5)

Truncates the leftmost characters, leaving the five rightmost characters.

UnitPrice

Nz([UnitPrice])

Changes a null (undefined or unknown) value to a zero (0) in the UnitPrice field.

SELECT [FirstName],[LastName] FROM [Employees] WHERE [LastName]="Danseglio";

Displays the values in the FirstName and LastName fields for employees whose last name is Danseglio.

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;

Calculates the average extended price for orders for which the value in the ExtendedPrice field is more than 1000, and displays it in a field named Average Extended Price.

SELECT [CategoryID], Count([ProductID]) AS [CountOfProductID] FROM [Products] GROUP BY [CategoryID] HAVING Count([ProductID])>10;

In a field named CountOfProductID, displays the total number of products for categories with more than 10 products.

Quantity

1

1

Region

"MT"

MT

Region

"New York, N.Y."

New York, N.Y. (Note that you must enclose the value in quotation marks if it includes punctuation.)

Fax

""

A zero-length string to indicate that, by default, this field should be empty instead of containing a null value

Order Date

Date( )

Today's date

DueDate

Date() + 60

The date 60 days forward from today

<> 0

Please enter a nonzero value.

0 Or > 100

Value must be either 0 or more than 100.

Like "K???"

Value must be four characters, beginning with the letter K.

< #1/1/2017#

Enter a date prior to 1/1/2017.

>= #1/1/2017# And < #1/1/2008#

Date must occur in 2017.