Sum Min Max Avg ParamArray.
I’m sure your first reaction after reading the title might be, “I already know all that—tell me something new!” If you haven’t come across the last item in the title (the odd one out), then that’s exactly what I plan to share here—so keep reading.
The first four terms are quite familiar; they refer to built-in functions in Microsoft Access and worksheet functions in Excel. We’ll get to the last one a little later, but first, let’s look at how the Min() function works in Excel—and why using it in Microsoft Access presents a few challenges in comparison.
Difference between Excel and Access.
We’re certainly not forgetting the other domain aggregate functions in Access—DCount(), DSum(), DMin(), DMax(), and DAvg().
Let’s start by looking at how the Min() worksheet function works in Excel. It can identify the minimum value from a range of cells in a single column, a row of cells across multiple columns, or even a block of cells spanning several rows and columns.
However, when we return to Microsoft Access, the Min() function behaves differently. It can only be applied to a single column (that is, a single field) within a query, or in the header and footer sections of forms or reports. So, how do we determine the minimum value across multiple fields?
Go through the sample table below to better understand the issue we’re dealing with.
We have received Quotations for Electronic Items from three different Suppliers, and we need to know which quotation is the lowest and from which Supplier. In this case, our Min() Function has no use here unless we reorganize the above data in the following format:
To obtain the desired result from this data, we’ll need to create two queries, setting aside—for now—issues such as duplicate descriptions, supplier names, or the overall table size.
-
First Query (Total Query):
Group the data by the Desc field and use the Min() function to determine the lowest value from the Values field. -
Second Query:
Use both the original table and the first query as data sources. Join them on the Desc and MinOfValues fields from the Total Query with the Desc and Values fields of the base table. This will return all records from the table that match both the description and the lowest quoted value.
The ParamArray Method.
I consider these steps to be excessive work, and I know you will agree too. Instead, we can write a User Defined Function with the use of ParamArray and pass the Field Names to the Function and find the Minimum Value from the list. Here is a simple Function with the use of the ParamArray declaration to find the Minimum Value from a List of Values passed to it.
Public Function myMin(ParamArray InputArray() As Variant) As Double
'------------------------------------------------------------------
'Author : a.p.r. pillai
'Date : November-2008
'URL : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------
Dim arrayLength As Integer, rtn As Double, j As Integer
'calculate number of elements in Array
arrayLength = UBound(InputArray())
'initialize Null values to 0
For j = 0 To arrayLength
InputArray(j) = Nz(InputArray(j), 0)
Next
'initialize variable with 1st element value
'or if it is zero then a value with high magnitude
rtn = IIf(InputArray(0) = 0, 9999999999#, InputArray(0))
For j = 0 To arrayLength
If InputArray(j) = 0 Then
GoTo nextitem
If InputArray(j) < rtn Then
rtn = InputArray(j)
End If
nextitem:
Next
myMin = rtn
End FunctionCopy and paste the above Code into a Global Module and save it.
A few simple rules must be kept in mind while writing User Defined Functions using the ParamArray declaration in the Parameter list of the Function.
While declaring the Function, the Parameter Variable InputArray() (or any other name you prefer) must be declared with the keyword ParamArray, in place of ByRef or ByVal we normally use to declare parameters to functions.
The Data Type must be a Variant type.
The ParamArray declaration must be the last item in the Parameter list if the UDF accepts more than one Parameter.
The Optional parameter declarations should not appear before the ParamArray declaration.
Since the data type is Variant, it can accept any value type.
Using the above myMin() Function, we have created a Query on the first Table given above. The SQL and the resulting image of the Query in Datasheet View are shown below.
SELECT MaterialQuote.Desc, MaterialQuote.Supplier1, MaterialQuote.Supplier2, MaterialQuote.Supplier3, mymin([supplier1], [supplier2], [supplier3]) AS Minimum, IIf([minimum]=[supplier1],"Supplier1",IIf([minimum]=[supplier2],"Supplier2",IIf([minimum]=[supplier3],"Supplier3",""))) AS Quote FROM MaterialQuote;
In the above example, we have used only three Field Values to pass to the Function, and these can vary depending on your requirement.
Modified Version of VBA Code
A modified version of the same function is given below that accepts a Calculation Type value (range 0 to 3) as the first Parameter, and depending on that, we can find the Summary, Minimum, Maximum, or Average values passed to it through the InputArray() Variable.
Option Compare Database
Enum SMMA
accSummary = 0
accMinimum = 1
accMaximum = 2
accAverage = 3
End Enum
Public Function SMMAvg(ByVal calcType As Integer, ParamArray InputArray() As Variant) As Double
'------------------------------------------------------------------------
'calType : 0 = Summary' : 1 = Minimum
' : 2 = Maximum' : 3 = Average
'------------------------------------------------------------------------
'Author : a.p.r. pillai'Date : November 2008
'URL : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------------
Dim rtn As Double, j As Integer, arrayLength As Integer
Dim NewValue As Variant
On Error GoTo SMMAvg_Err
If calcType < 0 Or calcType > 3 Then
MsgBox "Valid calcType Values 0 - 3 only", , "SMMAvg()"
Exit Function
End If
arrayLength = UBound(InputArray())
'Init Nulls, if any, to 0
For j = 0 To arrayLength
InputArray(j) = Nz(InputArray(j), 0)
Next
For j = 0 To arrayLength
NewValue = InputArray(j)
'skip 0 value
If NewValue = 0 Then
GoTo nextitem
End If
Select Case calcType
'Add up values for summary/average
Case accSummary, accAverage
rtn = rtn + NewValue
Case accMinimum
rtn = IIf(NewValue < rtn, NewValue, rtn)
rtn = IIf(rtn = 0, 9999999999#, rtn)
Case accMaximum
rtn = IIf(NewValue > rtn, NewValue, rtn)
End Select
nextitem:
Next
'Calc Average
If calcType = accAverage Then
rtn = rtn / (arrayLength + 1)
End If
SMMAvg = rtn
SMMAvg_Exit:
Exit Function
SMMAvg_Err:
MsgBox Err.Description, , "SMMAVG()"
SMMAvg = 0
Resume SMMAvg_Exit
End Function
The Function name was defined using the first letters of the Calculation Types that the Function can perform, and I hope you like it too.
When any of the values in the InputArray() element is zero, that is ignored and will not be taken as the minimum value.
Sample Runs on Immediate Window:
? SMMAvg (0,0,10,5,7) 'Summary Result: 22 ? SMMAvg (1,0,10,5,7) 'Minimum value from 0,10,5,7 Result: 5 ? SMMAvg (2,0,10,5,7) 'Maximum value from 0,10,5,7 Result: 10 ? SMMAvg (3,0,10,5,7) 'Average Result: 5.5
We can use this Function in Text Boxes on Forms, Reports, or from other Controls. Use it at your own risk.
Download Demo Database Access2007.zip
