Excel's MAX function in Access

[Marios]

Hello all.

Is there a way to implement Excel's MAX function in Access?

e.g. MAX(field1 ; field2; field3)

Thanks a lot

 

[BeWyched]

Hi

Use:

DMax("field", "table", "condition")

Leave out the "condition" to search all records:

e.g.

varMax = DMax("Quantity", "Orders")

Cheers.

BW

 

[Marios]

Thanks for the answer, but what I want to do is this:

I have a query with several fileds on it. I would like to get the MAX from
two different fields (field1 , field 2) for each row, not the maximum value
stored in a specific field

Thanks

Ο χÏήστης "BeWyched" έγγÏαψε:

 

[Arvin Meyer [MVP]]

Two different fields may not contain maximum values on any specific row.
That is to say. 1 row may have the maximum value in 1 field, while the
maximum on the second field is on a different row.

 

[Douglas J. Steele]

Function MyMax(ParamArray Values()) As Variant
Dim intLoop As Integer
Dim varMax As Variant

If IsMissing(Values) Then
MyMax = Null
Else
For intLoop = LBound(Values) To UBound(Values)
If varMax < Values(intLoop) Then
varMax = Values(intLoop)
End If
Next intLoop
MyMax = varMax
End If

End Function


From the Immediate window:

?MyMax
Null
?MyMax(1, 3, 5)
5
?MyMax(11, 3, 5)
11

 

[BeWyched]

Hi Marios

You say there are 2 fields.

If so you could use the following SQL:

"SELECT IIf([field1]>[field2],[field1],[field2]) AS MaxOfFields
FROM [table1];"

MaxOfFields will return the maximum value of field1 vs. field2.

If there are more than 2 fields just build the IIf statement to suit (as per
Excel).

Cheers.

BW

 

[David W. Fenton]

Is there a way to implement Excel's MAX function in Access?

e.g. MAX(field1 ; field2; field3)

Here's my iMax() function:

Public Function iMax(ParamArray p()) As Variant
' Idea from Trevor Best in Usenet MessageID:
' (e-mail address removed)
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

The corresponding iMin() is this:

Public Function iMin(ParamArray p()) As Variant
' Idea from Trevor Best in Usenet MessageID:
' (e-mail address removed)
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

 

[GBAccess]

I Love This!!!

I also need to do the inverse, find the minimum value. I thought I could
make the adjustment myself, but I guess I'm brain-dead today. Can you advise

 

[vanderghast]

Replace the > by a <.


Vanderghast, Access MVP

I Love This!!!

I also need to do the inverse, find the minimum value. I thought I could
make the adjustment myself, but I guess I'm brain-dead today. Can you
advise

 

[GBAccess]

That's what I thought, but when I tried that I wind up with a null value out
of 3 possible values:1,3 & 5. Any ideas why?

Here's how it looks if this helps (by the way, thank you for helping me out!)
Function MyMin(ParamArray Values()) As Variant
Dim intLoop As Integer
Dim varMin As Variant

If IsMissing(Values) Then
MyMin = Null
Else
For intLoop = LBound(Values) To UBound(Values)
If varMin > Values(intLoop) Then
varMin = Values(intLoop)
End If
Next intLoop
MyMin = varMin
End If

End Function

 

[Dirk Goldgar]

That's what I thought, but when I tried that I wind up with a null value
out
of 3 possible values:1,3 & 5. Any ideas why?

Here's how it looks if this helps (by the way, thank you for helping me
out!)
Function MyMin(ParamArray Values()) As Variant
Dim intLoop As Integer
Dim varMin As Variant

If IsMissing(Values) Then
MyMin = Null
Else
For intLoop = LBound(Values) To UBound(Values)
If varMin > Values(intLoop) Then
varMin = Values(intLoop)
End If
Next intLoop
MyMin = varMin
End If

End Function

I overlooked that we're starting with a Null value in varMin, which won't
compare to anything. We'd probably also do well to allow for possible Null
values in the argument list. Try this version:

'------ start of code ------
Function MyMin(ParamArray Values()) As Variant

Dim intLoop As Integer
Dim varMin As Variant
Dim ListVal As Variant

If IsMissing(Values) Then
MyMin = Null
Else

varMin = Null

For intLoop = LBound(Values) To UBound(Values)
ListVal = Values(intLoop)
If Not IsNull(ListVal) Then
If IsNull(varMin) Then
varMin = ListVal
Else
If varMin > ListVal Then
varMin = ListVal
End If
End If
End If
Next intLoop

MyMin = varMin

End If

End Function
'------ end of code ------

 

[GBAccess]

That did the trick! Thank you so much for your help