Highest value from three fields

[Jason Silva]

Hello,

I have three fields in the same table named, CurBal, OrgAmt, and LOCAmt. I would like a query field that only shows the highest of these three fields. Any thoughts?

Something Like: Value: Highest([CurBal], [OrgAmt], [LOCAmt])

Thanks,

 

[Allen Browne]

From the Module tab of your database window, choose New.
Paste in the function below. Save. ("Module1" will do.)

Now in your query you can use:
Largest([CurBal], [OrgAmt], [LOCAmt])

The function accepts any number of arguments.


Function Largest(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.

varMax = Null 'Initialize to null

For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
End If
End If
Next

Largest = varMax
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I have three fields in the same table named, CurBal, OrgAmt, and LOCAmt. I
would like a query field that only shows the highest of these three fields.
Any thoughts?

Something Like: Value: Highest([CurBal], [OrgAmt], [LOCAmt])

 

[Jason Silva]

Like a charm, Thanks. That will make the auditors happy.