|
|
Examples of expressions
used in Update query
Use expressions (expression
is 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 for the field you
want to update.
|
Expression |
Result |
|
IIf(IsNull([UnitPrice]), 0, [UnitPrice]) |
Changes a Null in
the UnitPrice field.
(Null is 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) |
|
IIf(IsNumeric([Material])=True,CStr(Val([Material])),[Material]) |
If the Material field is
numeric, it converts the numbers to string. CStr function
converts a Variant value to the String data type. |
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. |
|
"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. |
|
