Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Sum Min Max Avg ParamArray

Introduction.

I know your immediate response after looking at the title will be, "I know all those things, tell me something that I don't know about". Well, if you haven't come across the last item (that is an odd one) in the Title before, then that is what I am trying to do here, read on. The first four words are very familiar to us they are Built-in Functions in MS-Access and Worksheet Functions in Excel.

We will catch up with the last one later, after checking the usage of Min() Function (will represent the first four items in the title) in Excel and why we have some difficulty with it in MS-Access when compared with Microsoft Excel.

Difference between Excel and Access

We are not forgetting the other Functions DCount(), DSum(), DMin(), DMax() and DAvg() of Access at all.

Let us look at the usage of the Min() Worksheet Function in Excel. It can find the minimum Value from a Range of Cells in a Single Column, from a Row of Cells across Columns, or from a Range of Cells spread over several Columns and Rows.

But, when we come back to MS-Access the Min() function can be used only in a single column (on a single Field) of a Query and in Header/Footer Sections of Forms or Reports. Then what do we do to find the Minimum value from more than one Field?

Have a look at the sample table given below to get the gravity of the issue we are here.

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 re-organize the above data in the following format:

To get the required result out of this data, we need two Queries and we will ignore the duplication of Descriptions, Supplier Names, the Table size in Records, etc., for now.

  1. Need one Total Query to the group on the Desc field and the Min() Function to find the minimum value from the Values Field.
  2. Need a second Query, using the first Query and the Table above as Source, JOINed on Desc and MinOfValues Columns of the Total Query with the Desc and Values Fields of the Table to pick all the records from the Table matching with minimum quoted values and Description.

The ParamArray Method

I consider these steps are 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.

  1. 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.
  2. The Data Type must be a Variant type.
  3. The ParamArray declaration must be the last item in the Parameter list if the UDF accepts more than one Parameter.
  4. The Optional parameter declarations should not appear before the ParamArray declaration.
  5. Since the data type is Variant it can accept any type of value.

With the use of 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 given 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 then 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.

Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code