IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, December 2004. Updated July 2006
Access does not have functions like Min() and Max() in Excel, for selecting the least/greatest value from a list. That makes sense in a relational database, because you store these values in a related table. So Access provides DMin() and DMax() for retrieving the smallest/largest value from the column in the related table.
Occasionally, you still need to pick the minimum or maximum value from a list. The functions below do that. They work with numeric fields, including currency and dates. They return Null if there was no numeric value in the list.
To create them:
Use them like any built-in function.
For example, you could put this in a text box:
=MinOfList(5, -3, Null, 0, 2)
Or you could type this into a fresh column of the Field row in a query that has three date fields:
MaxOfList([OrderDate], [InvoiceDate], [DueDate])
Function MinOfList(ParamArray varValues()) As Variant Dim i As Integer 'Loop controller. Dim varMin As Variant 'Smallest value found so far. varMin = Null 'Initialize to null For i = LBound(varValues) To UBound(varValues) If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then If varMin <= varValues(i) Then 'do nothing Else varMin = varValues(i) End If End If Next MinOfList = varMin End Function Function MaxOfList(ParamArray varValues()) As Variant Dim i As Integer 'Loop controller. Dim varMax As Variant 'Largest value found so far. varMax = Null 'Initialize to null For i = LBound(varValues) To UBound(varValues) If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then If varMax >= varValues(i) Then 'do nothing Else varMax = varValues(i) End If End If Next MaxOfList = varMax End Function
The ParamArray keyword lets you pass in any number of values. The function receives them as an array. You can then examine each value in the array to find the highest or lowest. The LBound() and UBound() functions indicate how many values were passed in, and the loop visits each member in the array.
Any nulls in the list are ignored: they do not pass the IsNumeric() test.
The return value (varMin or VarMax) is initialized to Null, so the function returns Null if no values are found. It also means that if no values have been found yet, the line:
If varMin <= varValues(i) Then
evaluates to Null, and so the Else block executes. Since an If statement has three possible outcomes - True, False, and Null - a "do nothing" for one is a convenient way to handle the other two. If that is new, see Common errors with Null.
Note that the functions would yield wrong results if the return value was not initialized to Null. VBA initializes it to Empty. In numeric comparisons, Empty is treated as zero. Since the function then has a zero already, it would then fail to identify the lowest number in the list.
|Home||Index of tips||Top|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'MinOfList() and MaxOfList() functions'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...