.You enter the expression in the Field cell in the query design grid. The fields you reference in the expression are from the tables or queries in the current query.
| Expression | Description |
|---|---|
| PrimeFreight: [Freight] * 1.1 | Displays freight charges plus 10 percent in the PrimeFreight field. |
| OrderAmount: [Quantity] * [UnitPrice] | Displays the product of the values in the Quantity and UnitPrice fields in the OrderAmount field. |
| LeadTime: [RequiredDate] - [ShippedDate] | Displays the difference between the values in the RequiredDate and ShippedDate fields in the LeadTime field. |
| TotalStock: [UnitsInStock]+[UnitsOnOrder] | Displays the sum of the values in the UnitsInStock and UnitsOnOrder fields in the TotalStock field. |
| 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. (This example uses the Sum function.)
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. |
Note 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; for example:
Nz([UnitsInStock],0)+Nz([UnitsOnOrder],0)
For more information on the Nz function, click .