By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,529 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Maximum number between fields

P: n/a
Hi,

I want to find out how to find the maximum number from a number of
different fields .

ie find the max number between field1, field2 and field3 in a table
using a query

Regards,

Denis

Jan 16 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Denis wrote:
Hi,

I want to find out how to find the maximum number from a number of
different fields .

ie find the max number between field1, field2 and field3 in a table
using a query

Regards,

Denis
Probably a VBA function is the most efficient way to find MaxOf per row,
then use SQL to get the Max() of the MaxOf:

(not tested)

Public Function MaxOf (Val1, Val2, Val3) As Double
Dim tmp1 as double
Dim tmp2 as double
Dim tmp3 as double
tmp1 = Abs(Val1-Val2)
tmp2 = Abs(Val1-Val3)
tmp3 = Abs(Val2-Val3)
If tmp1 tmp2 and tmp1 tmp3 Then
MaxOf = tmp1
ElseIf tmp2 tmp3 Then
MaxOf = tmp2
Else
MaxOf = tmp3
EndIf
End Function

SELECT Max(MaxOf(Field1, Field2, Field3))
GROUP BY ???;

--
Smartin
Jan 17 '07 #2

P: n/a

"Denis" <de*********@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.com...
Hi,

I want to find out how to find the maximum number from a number of
different fields .

ie find the max number between field1, field2 and field3 in a table
using a query
The following function will find the maximum of a list of numbers:

Function MaxN(ParamArray N() As Variant) As Variant
Dim i As Integer
For i = 0 To UBound(N)
If N(i) MaxNThen MaxN = N(i)
Next i
End Function

The number of numbers in the list need not be the same each time the
function is called.

Larry Linson
Microsoft Access MVP
Jan 18 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.