Finding out Maximum and Minimum Value's - The DMax and DMin Functions

So we have a table of sales figures for 2011.  Suppose we want to find out the largest and smallest sales value for the month of May. Access has two functions ideal for this task - these are the DMax and DMin functions.

DMax and DMin basically examine a domain of values, returning the largest and smallest value respectively.  The syntax for using these functions follow the same format as the other domain related functions such as DLookUp and DSum which I blogged about last year.  As such, we pass parameters for field name, the domain from which the field belongs (the table or query, for example), and an optional criteria if we are just interested in particular records (such as May sales for example).

Lets take a look at the syntax for DMax and DMin respectively:

DMax("fieldName", "tableName", "criteria")

DMin("fieldName", "tableName", "criteria")

This is the table that we are going to use in our particular scenario:

If you remember, we are interested in the largest and smallest sale during the month of May.  As such, we are going to pass the field name "Total" for the first parameter, the table name "tblOrders" for the second, and the criteria "OrderDate >= #05/01/2011# and OrderDate <= #05/31/2011#" for the third.  Note the third criteria is basically a SQL WHERE Statement (without the WHERE keyword) and uses the American Date Format (ie  Month/Day/Year), rather than the International Date Format.  This is the full syntax:

DMin("Total", "tblOrders", "OrderDate >= #5/1/2011# and OrderDate <= #5/31/2011#")

In previous posts I have shown you how to use Access Functions in Queries and Calculated Controls (see posts on Calculating Date Difference and  DLookUp).  For this example I would like to use the function within a VBA sub procedure.

I have created a form with an unbound text box called txtResult and two command buttons, cmdMax and cmdMin.  When the user clicks one of the command buttons - lets say it is the cmdMax button - the VBA procedure containing the DMax function runs, calculating the maximum sale for the month of May, and displaying the result in txtResult.

Here is the VBA code that I used:


Private Sub cmdMax_Click()
    Dim varMax As Currency
    varMax = DMax("Total", "tblOrders", "OrderDate >= #5/1/2011# and OrderDate <= #5/31/2011#") 
    Me!txtResult = varMax
End Sub


Private Sub cmdMin_Click()
    Dim varMin As Currency
    varMin = DMin("Total", "tblOrders", "OrderDate >= #5/1/2011# and OrderDate <= #5/31/2011#")
    Me!txtResult = varMin
End Sub


As you can see each sub runs when it's respective command button's ONCLICK event is triggered.  The result returned by the function is stored in a currency type variable called varMax or varMin.  The value of txtResult is then set to that of the variable in order to display the result.

You can download this DMax/Min example database by clicking the link.  Please feel free to experiment  using different criteria and adding new data to the table etc.