DMin, DMax Functions Example

The following example returns the lowest and highest values from the Freight field for orders shipped to the United Kingdom. The domain is an Orders table. The criteria argument restricts the resulting set of records to those for which ShipCountry equals UK.

Dim curX As Currency, curY As Currency
curX = DMin("[Freight]", "Orders", "[ShipCountry] = 'UK'")
curY = DMax("[Freight]", "Orders", "[ShipCountry] = 'UK'")

In the next example, the criteria argument includes the current value of a text box called OrderDate. The text box is bound to an OrderDate field in an Orders table. Note that the reference to the control isn't included in the double quotation marks (") that denote the strings. This ensures that each time the DMax function is called, Microsoft Access obtains the current value from the control.

Dim curX As Currency
curX = DMax("[Freight]", "Orders", "[OrderDate] = #" _
    & Forms!Orders!OrderDate & "#")

In the next example, the criteria expression includes a variable, dteOrderDate. Note that number signs (#) are included in the string expression, so that when the strings are concatenated, they will enclose the date.

Dim dteOrderDate As Date, curX As Currency
dteOrderDate = #3/30/95#
curX = DMin("[Freight]", "Orders", _
    "[OrderDate] = #" & dteOrderDate & "#")