DMin & Dmax Domain Aggregate Functions in Access

Microsoft Access Min Max Domain Aggregate Functions

Finding the minimum and maximum values for a record set is best done using the min and max function of a 'totaled' query.

However, for completeness we include the examples of these functions here. Note that the average aggregate function (Davg) has the same format & syntax as the Dmin and Dmax examples below.

In our Employees table, query or view setup we have a list of employees and each of their current salaries along with their departments:

Employee_ID
Salary
Department

An unbound form or report field would have the following Record Source to calculate the minimum value in the recordset:

=DMin("Salary","Employees")

To restrict the minimum to a specific department we add the restriction clause:

=DMin("Salary","Employees","Department='Shipping' ")

If records are grouped by department on the form or report you can use the recordset field name to restrict the scope of the DMin and DMax functions:

=DMin("Salary","Employees","Department='" & Department & "'")

Note that the field name department is bounded by single quote marks ( ' ) and those quote marks have double quote marks delineating them which makes figuring out the above example a little difficult.  Here's the same command that may be a little easier to read:

=DMin("Salary","Employees","Department=" & Chr(34) & Department & Chr(34))

Rapping a chr(34) around the department recordset field simply enclosed it in double quote marks.

The DMax functions are the same... just substitute DMin with DMax.