Highest Value in Record 3

[james2308]

I have a table with 3 date fields. At least one of them is always filled in, sometimes all of them. In a query, I need to return the single highest value of these fields for each record.

Does anyone have any idea how I can do this? It is starting to hurt my brain.

Many thanks,

James

 

[MichaelRed]

Public Function basMaxVal(ParamArray varMyVals() As Variant) As Variant

    'Michael Red 10/25/2001
    'To return the MAXIMUM or a series of values

    Dim Idx As Integer
    Dim MyMax As Variant

    For Idx = 0 To UBound(varMyVals())
        If (IsMissing(varMyVals(Idx))) Then
            GoTo NextVal
        End If
        If (varMyVals(Idx) > MyMax) Then
            MyMax = varMyVals(Idx)
        End If
NextVal:
    Next Idx

    basMaxVal = MyMax

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[james2308]

Michael,

I can see what this does, tahnk you. However (and I am showing my shallow knowledge here), how do I apply this to my query?

Thanks for all the help, i really appreciate it.

James.

 

[Craig0201]

Michael,

Pure genius.....

Worth a star by any standards....

Craig

 

[MichaelRed]

MyMax: basMaxVal(Field1, Field2, ...Fieldn)

Craig, et al. Thanks. Kudos (Stars ... ) and JOB OFFERS Welcomed.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[sedgely]

Thanx, this half solves my problem... I'm trying to return the earliest of 4 time fields - with the added complications of midnight and some if not all of the fields may be blank.
Any ideas would be gratefully received. B4 my boss gives me a good slapping LOL