Selecting highest value across several fields

[weekend00]

I think you can do this thing in VBA code instead of in a query with function.

or

modify the maximum function to use sample id as the parameter, and look for the max field:

Public Function MaxField(pKeyValue As Variant) As Double
    Const tblName = "mytable"
    Const pKey = "sample"
    Const FieldList = "f2,f3,f5"
    
    Dim rs As DAO.Recordset
    Dim keystr As String
    Dim i As Long
    If VarType(pKeyValue) = vbString Then
        keystr = pKey & "='" & pKeyValue & "'"
    Else
        keystr = pKey & "=" & pKeyValue
    End If
    Set rs = CurrentDb.OpenRecordset("select " & FieldList & " from " & tblName & " where " & keystr)
    If Not rs.EOF Then
        MaxField = -100000000#
        For i = rs.Fields.Count - 1 To 0 Step -1
            If MaxField < rs.Fields(i).Value Then MaxField = rs.Fields(i).Value
        Next
    Else
        MaxField = Null
    End If
End Function

[megabrown]

There we go, here's a sample of the db.

Sweet, Ill try the modified max function

[pbaldy]

I think you're hitting the character limit for the query grid. I didn't want to go the way of a custom function if I could get away with it, but it looks like you'll have to go that way. Try weekend's and see how it goes. Off the top of my head it looks okay except for this:

MaxField = Null

which will error due to the Double data type.

[Dawmah]

hi Paul.
I need a code or query that will calculate the two most high values within a record still over different fields.Actually I choose out of four values.

[Dawmah]

I need a code or query that will return the two most high values within a record still over different fields.Actually I choose out of four values.