How to get the largest value of a few columns? 1

[lihuitsai]

I am trying to get the maximum out a few fields in the same table. For example: I have a table with three fields: D1, D2, and D3. I wanted to write a query that produce the Maximum [D1, D2, and D3] and assign it to D4. The built-in Max function does not work because it produces the largest value of one field over all records. I am trying to get the largest of a few fields from the same record.
Thanks,
LiHui

 

[PHV]

Create the following function in a standard code module:

'A generic function to get the max value of an arbirtrary numbers of same type values:
Public Function myMax(ParamArray Args())
Dim i As Long, rv
rv = Args(LBound(Args))
For i = 1 + LBound(Args) To UBound(Args)
  If IsNull(rv) Or rv < Args(i) Then rv = Args(i)
Next
myMax = rv
End Function

And now in the query grid:
D4: myMax([D1],[D2],[D3])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244