Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Calculate Fields in Domain Aggregate Functions [Access 2003 VBA Language Reference]

Office 2003

You can 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, then calculate the average. Enter the following example in a procedure in a module.

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

© 2015 Microsoft