Edit

Calculate fields in domain aggregate functions

  • Article

Use the string expression argument (the expr argument) in a domain aggregate function to perform a calculation on values in a field. For example, you can calculate a percentage (such as a surcharge or sales tax) by dividing a field value by a number.

The following table provides examples of calculations on fields from an Orders table and an Order Details table.

Calculation Example
Add a number to a field "[Freight] + 5"
Subtract a number from a field "[Freight] - 5"
Multiply a field by a number "[Freight] * 2"
Divide a field by a number "[Freight] / 2"
Add one field to another "[UnitsInStock] + [UnitsOnOrder]"
Subtract one field from another "[ReorderLevel] - [UnitsInStock]"

You would most likely use a domain aggregate function in a macro or module, in a calculated control on a form or report, or in a criteria expression in a query.

For example, you can calculate the average discount amount for all orders in an Order Details table. Multiply the Unit Price and Discount fields to determine the discount for each order, and then calculate the average. Enter the following example in a procedure in a module.

Dim dblX As Double 
dblX = DAvg("[UnitPrice] * [Discount]", "[Order Details]")

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.