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 function and Dmax function 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.

Open Email Using SendObject Access Download User Inactivity Logout VBA Code Disable Shift Key Demo Send Email Microsoft Access Tutorial Download (Advanced) Sequential Counter in Query Multi Select List Box Query Parameters Row Level Data Security Programming MS AccessSecurity Alternative Single User Inventory Calculations Download How To PerformMulti-User Inventory Calculations Make Dependent Combo Box Code MS Access Tutorial Union Query Example (Simple) Union Query (Advanced) Access Tutorial How ToFill Fields From Combo Box Use Global Variables as Parameters Continuous Form Dependent Combo Box How To Program Continuous-Continuous Master/Detail Forms Access Bar Chart / Bar Graph Programming Crosstab Query Example TransferText & OutputTo Microsoft Access DoCmd.OpenForm & OpenArgs VBA Example Running Sum Query Method Choose Command Dynamic SQL Order By Access Conditional Format Access Report Banding

Popup Form Control Method

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016