No max(x,y) function in Access

access query min and max
ms access max date
ms access max number
access query max date not working
ms access having max
access select max date for multiple records
access query max minus 1
maxof access

VBA for Access lacks a simple Max(x,y) function to find the mathematical maximum of two or more values. I'm accustomed to having such a function already in the base API coming from other languages such as perl/php/ruby/python etc.

I know it can be done: IIf(x > y, x,y). Are there any other solutions available?

I'll interpret the question to read:

How does one implement a function in Access that returns the Max/Min of an array of numbers? Here's the code I use (named "iMax" by analogy with IIf, i.e., "Immediate If"/"Immediate Max"):

  Public Function iMax(ParamArray p()) As Variant
  ' Idea from Trevor Best in Usenet MessageID rib5dv45ko62adf2v0d1cot4kiu5t8mbdp@4ax.com
    Dim i As Long
    Dim v As Variant

    v = p(LBound(p))
    For i = LBound(p) + 1 To UBound(p)
      If v < p(i) Then
         v = p(i)
      End If
    Next
    iMax = v
  End Function

  Public Function iMin(ParamArray p()) As Variant
  ' Idea from Trevor Best in Usenet MessageID rib5dv45ko62adf2v0d1cot4kiu5t8mbdp@4ax.com
    Dim i As Long
    Dim v As Variant

    v = p(LBound(p))
    For i = LBound(p) + 1 To UBound(p)
      If v > p(i) Then
         v = p(i)
      End If
    Next
    iMin = v
  End Function

As to why Access wouldn't implement it, it's not a very common thing to need, seems to me. It's not very "databasy", either. You've already got all the functions you need for finding Max/Min across domain and in sets of rows. It's also not very hard to implement, or to just code as a one-time comparison when you need it.

Maybe the above will help somebody.

Min, Max Functions - Access, Use the Min and Max functions in Access to return the minimum or maximum of a intrinsic or user-defined but not one of the other SQL aggregate functions). Max. Return the maximum value from the result set of a query. (SQL). Syntax Max (expression) Key expression A field or formula that evaluates to a numeric expression. Use the Max function in the access query builder by clicking the Totals toolbar button: Σ. The Max function is used in conjunction with the Group By clause. Example

Calling Excel VBA Functions from MS Access VBA

If you add a reference to Excel (ToolsReferencesMicrosoft Excel x.xx Object Library) then you can use WorksheetFunction to call most Excel worksheet functions, including MAX (which can also be used on arrays).

Examples:

MsgBox WorksheetFunction.Max(42, 1999, 888)

or,

Dim arr(1 To 3) As Long
arr(1) = 42
arr(2) = 1999
arr(3) = 888
MsgBox WorksheetFunction.Max(arr)

The first call takes a second to respond (actually 1.1sec for me), but subsequent calls are much more reasonable (<0.002sec each for me).


Referring to Excel as an object

If you're using a lot of Excel functions in your procedure, you may be able to improve performance further by using an Application object to refer directly to Excel.

For example, this procedure iterates a set of records, repeatedly using Excel's MAX on a Byte Array to determine the "highest" ASCII character of each record.

Option Compare Text
Option Explicit
'requires reference to "Microsoft Excel x.xx Object Library"
Public excel As New excel.Application 

Sub demo_ListMaxChars()
    'list the character with the highest ASCII code for each of the first 100 records
    Dim rs As Recordset, mx
    Set rs = CurrentDb.OpenRecordset("select myField from tblMyTable")
    With rs
        .MoveFirst
            Do
                mx = maxChar(!myField)
                Debug.Print !myField, mx & "(" & ChrW(mx) & ")"  '(Hit CTRL+G to view)
                .MoveNext
            Loop Until .EOF
        .Close
    End With
    Set rs = Nothing     'always clean up your objects when finished with them!
    Set excel = Nothing  
End Sub

Function maxChar(st As String)
    Dim b() As Byte                             'declare Byte Array
    ReDim b(1 To Len(st))                       'resize Byte Array
    b = StrConv(st, vbFromUnicode)              'convert String to Bytes
    maxChar = excel.WorksheetFunction.Max(b)    'find maximum Byte (with Excel function)
End Function

Min, Max functions (Microsoft Access SQL), For example, you could use these functions to return the lowest and highest freight cost. If there is no aggregation specified, the entire table is  The Max function can be used in the following versions of Microsoft Access: Access 2016, Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000; Example in SQL/Queries. You can use the Max function in a query by clicking on the Totals button in the toolbar (This is the button with the summation symbol). The Max function is

Because they probably thought that you would use DMAX and DMIN or the sql MAX and only working with the database in access?

Im also curious about why.. Its seems like a overkill to have to create a temp-table and add form values to the table and then run a DMAX or MAX-query on the table to get the result...

MS Access Max() Function, Definition and Usage. The Max() function returns the maximum value in a set of values. Note: See also the Min() function. Using the MAX() function in Microsoft Access: The SQL Max( ) function retrieves the largest value in a specified column of a table or query. This can be useful when you need a fast way to find specific data in your database.

I've been known to create a small projMax() function just to deal with these. Not that VBA will probably ever be enhanced, but just in case they ever do add a proper Max (and Min) function, it won't conflict with my functions. BTW, the original poster suggests doing IIF... That works, but in my function, I usually throw a couple of Nz()'s to prevent a null from ruining the function.

MS Access: Max Function, The Microsoft Access Max function returns the maximum value in a set of numeric This website would not exist without the advertisements we display and your  No max (x,y) function in Access. VBA for Access lacks a simple Max (x,y) function to find the mathematical maximum of two or more values. I'm accustomed to having such a function already in the base API coming from other languages such as perl/php/ruby/python etc. I know it can be done: IIf (x > y, x,y).

Both functions have problems with Null. I think this will be better.

Public Function iMin(ParamArray p()) As Variant
  Dim vVal As Variant, vMinVal As Variant

  vMinVal = Null
  For Each vVal In p
    If Not IsNull(vVal) And (IsNull(vMinVal) Or (vVal < vMinVal)) Then _
      vMinVal = vVal
  Next

  iMin = vMinVal
End Function

JavaScript: The Definitive Guide: Master the World's Most-Used , Conditional property access is also possible using ? If either of those expressions has side effects, the side effect will not occur if a is not defined: let Math.max(x,y,z) // Math.max is the function; x, y, and z are the arguments. a.sort() // a.sort  The MAX function syntax has the following arguments: Number1, number2, Number1 is required, subsequent numbers are optional. 1 to 255 numbers for which you want to find the maximum value.

Shift Register Sequences: Secure And Limited-access Code , For every i, 1 s is k, let R = max (cf., 2" — cł), where c = X (f(x, , x, , , x.) however, no finer distinction is possible for H, since complementation of the function has effects on {T} already Let f(v, x, y, z) have the truth table shown in Figure VIII-2. The function f(x,y)= is computable 0 1x>y x≤y if if Turing Machine for Input: x0y Output: 1or 0 f(x,y)= 0 1x>y x≤y if if Turing Machine Pseudocode: Match a 1 from with a x1 from y • Repeat Until all of or is matched xy • If a 1 from is not matched erase tape, write 1 else erase tape, write 0 x (x>y) (x≤y)

Examples of expressions - Access - Office support, This article contains examples of expressions in Access to calculate values, validate data, Uses the IIf function to display the values of the City and PostalCode controls if the value in Uses the Count function to display the number of records in the OrderID control. =DateAdd("y", -10, [PromisedDate]). The IIf function is frequently used to create calculated fields in queries. The syntax is the same, with the exception that in a query, you must preface the expression with a field alias and a colon ( : ) instead of an equal sign ( = ).

How to use Min() and Max() functions in MS Access, We use the MS Access Min function when we need to define the smallest, namely the minimum value in a column of a table or a query. Let's look at its syntax: Min (​ 

Comments
  • You could use the Excel functions in Access. (More below.)
  • That's more useful than down votes a year after the question. :)
  • I'm surprised it didn't get an answer sooner, given how trivial the problem is. Perhaps it's because the question was confrontational (i.e., badmouthing Access) so that the people who knew the answer were too turned off to bother to try to help somebody who seems to have something of a chip on his shoulder.
  • You DO understand why a database development tool wouldn't have such a function built in, right? There are much greater omissions that are database-related than a function like this.
  • Can anybody point me to a more common solution that takes as function arguments not only array of numbers, but arrays and collections as well?
  • yeah, and the values I am using are in the database, but in two columns of the same row. It's just not an aggregate Max, but a simple mathematical Max.
  • Thank you for your contribution. Nulls do pop up and it is nice to have them handled.
  • IsNull(NumericItems) will never be True. If you meant to exit the function when called with no arguments, try checking for UBound(NumericItems) = -1
  • Good eye, used your suggestions on some sample data and it worked as advertized, keep it up!
  • A tad more information would be helpful, since this can't be used as-is. (I expanded on this below.)