Ms-Access Advanced VBA Programming Techniques, Tips and Tricks.

Sum Min Max Avg ParamArray

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". 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.

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

Let us look at the usage of 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 Data in 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 of data?

Have a look at the sample Table given below to get the gravity of the issue we are in 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 into the following format:

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

  1. Need one Total Query to group on 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.

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 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.

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 Variant.
  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 values.

With the use of the above myMin() Function we have created a Query on the first Table given above. The SQL and the result 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.

A modified version of the same Function is given below that accepts a Calculation Type value (range 0 to 3) as first Parameter and depending on that we can find Summary, Minimum, Maximum, or Average of values passed to it through the InputArray() Variable.

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, 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())
For j = 0 To arrayLength
   InputArray(j) = Nz(InputArray(j), 0)
Next

Select Case calcType
    Case 1
        rtn = InputArray(0)
        rtn = IIf(rtn = 0, 9999999999#, rtn)
    Case 2
        rtn = InputArray(0)
    Case Else
        rtn = 0
End Select

For j = 0 To arrayLength
    NewValue = InputArray(j)
    If NewValue = 0 Then
 GoTo nextitem
    Select Case calcType
        Case 0, 3
            rtn = rtn + NewValue
        Case 1
            rtn = IIf(NewValue < rtn, NewValue, rtn)
        Case 2
            rtn = IIf(NewValue > rtn, NewValue, rtn)
    End Select
nextitem:
Next

If calcType = 3 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 minimum value.

We can use this Function in Text Boxes on Forms or Reports by passing Values from other Controls. Use it at your own risk.

Share:

No comments:

Post a Comment

Trending

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Important Notice

Dear Readers, Very sorry to inform you that I am finding it difficult to renew my contract with the Hostgator Hosting Plans to continue prov...

Labels

Blog Archive

Recent Posts