Examples of expressions used in update queries - Access

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Printer-Friendly Version

Examples of expressions used in update queries

Applies to: Microsoft Office Access 2003

Use expressions (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) such as the following in the Update To cell in the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE 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 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.
Right([ShipPostalCode], 5)	Truncates the leftmost characters, leaving the five rightmost characters.
IIf(IsNull([UnitPrice]), 0, [UnitPrice])	Changes a Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.) value to a zero (0) in the UnitPrice field.