## No max(x,y) function in Access

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 (

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