## 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 (`Tools``References``Microsoft 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 (​

• `IsNull(NumericItems)` will never be `True`. If you meant to exit the function when called with no arguments, try checking for `UBound(NumericItems) = -1`