MS Access: DMax Function
The Microsoft Access DMax function returns the maximum value in a specified set of records (or domain).
DMax Function (Syntax)
The syntax for the DMax function is:
DMax ( expression, domain, [criteria] )
expression is the field that you wish to find the maximum value for.
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.
The DMax function can be used in the following versions of Microsoft Access:
- Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000
Let's look at a simple example:
DMax("UnitPrice", "Order Details", "OrderID = 10248")
In this example, you would return the maximum UnitPrice from the Order Details table where the OrderID is 10248. This is the same as the following SQL statement:
SELECT Max([Order Details].UnitPrice) AS MaxOfUnitPrice FROM [Order Details] WHERE ((([Order Details].OrderID)=10248));
DMax Examples (in VBA Code)
The DMax function can be used in VBA code in Microsoft Access. For example:
Dim LMax As Currency LMax = DMax("UnitPrice", "Order Details", "OrderID = 10248")
In this example, the variable called LMax would now contain the maximum UnitPrice from the Order Details table where the OrderID is 10248.
DMax Examples (in SQL/Queries)
You can also use the DMax function in a query in Microsoft Access.