Sum Min Max Avg ParamArray ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

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.

Right-click to open Large Image in New Tab/Window.

We have received Quotations for Electronic Items from three different Suppliers, and we need to know which one 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:

Right-click to open Large Image in New Tab/Window.

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 Function

Copy 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;

Right-click to open Large Image in New Tab/Window.

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.