Min & Max functions?

[Cris]

This does seem to be an oversight in the function department.

Be sure to familiarize yourself with the DMax and DMin functions for finding
the largest and smallest values of a field in a table. If you are needing
the MAX/MIN of a field within a group of records use DMax and DMin. But if
you are trying to find for example the max date within a single record, the
following is what you need. It does the same as Lotus' @Max function.

Create the following function in a Module: (you will have to create/copy
this function into any database you need this in.)

Public Function ListMax(ParamArray ListItems() As Variant)

Dim I As Integer

For I = 0 To UBound(ListItems())
If ListItems(I) > ListMax Then ListMax = ListItems(I)
Next I

End Function

To call this function use the following syntax:

ListMax(value1, value2, value3, ..., value(n))

Example:

Biggest = ListMax(12334, 345, 1234, 12345, 14352, 2345, 23456, 23465, 2346,
2345)

Biggest = ListMax([StartDate], [StopDate], [HireDate], [BirthDate], [Date1])

I'll leave you on your own to create a ListMin function.
(hint: simply change all ocurrances of "Max" with "Min".)

Cris

Dave Hensley wrote in message <348632a0...@news.connect.net>...
>Indulge an Access novice who's transitioning from Lotus spreadsheets.
>
>Lotus has a couple of functions, @min and @max, which evaluate a list
>of variables to return the lowest and highest, respectively variables
>in the list. For the life of me, I can't seem to find the equivalent
>functions in Access '97. I'm accomplishing the same functionality
>with IIF statements, but a MIN and MAX function would sure be simpler
>and quicker.
>
>Any help appreciated.
>
>_____________________________________________________________________
>Dave Hensley http://www.connect.net/dhensley/
>da...@connect.net Baseball is life...
>_____________________________________________________________________
>

[Dave Hensley]

[Bill Christensen]

You can run a query with the total row (click on View and Totals while in
query design view) set to min or max, or you can use domain aggregate
functions dmin and dmax (the slower method of the two)

Dave Hensley wrote in message <348632a0...@news.connect.net>...

[Kurt A. Fisher]

Dave Hensley <da...@connect.net> wrote in article
<348632a0...@news.connect.net>... <snip>
> Lotus has a couple of functions, @min and @max, . . . I can't seem to

> find the equivalent functions in Access '97.

It depends on the context:

If you are working with an SQL query in the query design window, the "Min"
and "Max" functions, as discussed in the examples and illustrated in Access
97 Help under those topics, can be used to return the minimum and maximum
values from a table or query. In the query by design window hit the
"sigma" button on the menu bar to start a total query. The "Total" line
on the grid (which displays the default "Group By") can be toggled to "Min"
or "Max". The query design grid generates SQL statements. An example SQL
statement from Access Help using the "Min" function is:

SELECT Min([Freight]) AS [LowFreight] FROM Orders WHERE [ShipCountry] =
'UK';

If you are working with calculated controls on a form or report or with
recordsets in VBA code modules, then you may wish to use the "DMin" and
"DMax" (for Domain minimum or Domain maximum), as discussed and illustrated
in the examples in Access 97 Help under those topics. An example SQL
statement from Access Help using the "DMin" function is:

curX = DMin("[Freight]", "Orders", "[ShipCountry] = 'UK'")

where "Freight" is a currency field in Table "Orders" limited to those
companies shipping from the "UK".

The VBA code and SQL methods illustrate above return the same value using
different syntax in different contexts.

Another related "spreadsheet" type function is DLookup which returns the
first occurrence in a table using a specified search/selection criteria.
See Access 97 Help.