Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Max of Expressions (WinXP/Office 2003)

    Is there an easy way, other than hairy nested IIF's to return the max of serveral expressions? For example Max(n1, n2, n3) where n1, n2 & n3 are expressions that give numeric results.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Max of Expressions (WinXP/Office 2003)

    Why not write a public function then use that in a query, eg GetMax(n1, n2, n3)

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max of Expressions (WinXP/Office 2003)

    Thanks for your interest. Can you suggest some code? Preferably, simple!!

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Max of Expressions (WinXP/Office 2003)

    This is air code, ie totally untested.

    Public Function GetMax(V1 as String, V2 as String, V3 as String) as String
    If V1 > V2 then
    If V1 > V3 then
    GetMax = V1
    Else
    If V3 > V2 then
    GetMax = V3
    End If
    End If
    Else
    If V2 > V3 then
    GetMax = V2
    Else
    GetMax = V3
    End If
    End If

    End Function

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Max of Expressions (WinXP/Office 2003)

    I learn something everyday, I didnot know about ParamArray as a way of defining an array as arguments to a function. Also UBound to find the number of items in the array.
    Nice solution that one.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Max of Expressions (WinXP/Office 2003)

    Activate the Modules tab of the database window.
    Click 'New'.
    Copy or type the following code:

    Function MaxVal(ParamArray Args())
    Dim i As Integer
    Dim RetVal
    RetVal = Args(0)
    For i = 1 To UBound(Args)
    If Args(i) > RetVal Then
    RetVal = Args(i)
    End If
    Next
    MaxVal = RetVal
    End Function

    And use it like this in a query

    MyMax: MaxVal(n1,n2,n3)

    or like this in the control source of a text box on a form or report:

    =MaxVal(n1,n2,n3)

    Of course, you must substitute something meaningful for n1, n2 and n3. The MaxVal function is not the most efficient possible, but it will work with a variable number of arguments, and with different types of arguments (numbers, dates, strings)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Max of Expressions (WinXP/Office 2003)

    Strictly speaking, UBound is not the number of items. If you declare an array as follows:

    Dim MyArray(3 To 7) As Integer

    LBound(MyArray) = 3 and UBound(MyArray) = 7, while the number of items is 7 - 3 + 1 = 5. For a ParamArray. the LBound is 0 (unless the module contains Option Base 1, in that case LBound = 1), and UBound = number of items - 1.

    Note: there was an error in my original code; I corrected this silently.

  8. #8
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max of Expressions (WinXP/Office 2003)

    Thanks to you both for a neat & comprehensive solution!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •