ADVERTISEMENT

MS Access: DAvg Function

---

In Access, the DAvg function returns the average of a set of numeric values in a specified set of records (or domain).

The syntax for the DAvg function is:

DAvg ( expression, domain, [criteria] )

expression is the numeric values that you wish to average.

domain is the set of records. This can be a table or a query name.

criteria is optional. It is the WHERE clause to apply to the domain.

For example:

Let's take a look at a simple example:

DAvg("UnitPrice", "Order Details", "OrderID = 10248")

In this example, you would be averaging the UnitPrice field in the Order Details table where the OrderID is 10248. This is the same as the following SQL statement:

SELECT Avg([Order Details].UnitPrice) AS AvgOfUnitPrice
FROM [Order Details]
WHERE ((([Order Details].OrderID)=10248));

You can also average more than one numeric field.  For example:

DAvg("UnitPrice * Quantity", "Order Details", "OrderID = 10248")

This example would average the UnitPrice x Quantity for all records in the Order Details table where the OrderID is 10248. This is the same as the following SQL statement:

SELECT Avg([UnitPrice]*[Quantity]) AS Expr1
FROM [Order Details]
WHERE ((([Order Details].OrderID)=10248));

VBA Code

The DAvg function can be used in VBA code. For example:

Dim LTotal As Currency

LTotal = DAvg("UnitPrice", "Order Details", "OrderID = 10248")

In this example, the variable called LTotal would now contain the average UnitPrice from the Order Details table where the OrderID is 10248.

SQL/Queries

You can also use the DAvg function in a query.