How to return Max value from 5 columns for 1 record?

[jacobsenm]

Help please,

I'm trying to run a query on a table set up as follows.

Name Date 1 Date 2 Date 3
Joe Bloggs 01/04/04 01/05/04 01/03/04

I need a function to return Joe bloggs in 1 column and 01/05/04 in the second column.

The MAX() function only seems to return the Maximum value down the specific column not across the records.

Thanks
Mark

 

[Norie]

Mark

You could probably write some sort of code in a function to do this.

Something like this, which could be used in a query.

Function MaxDate(MyName As String) As Date
Dim I As Integer
Dim myDate As Date
    
    
    For I = 1 To 3
        myDate = DLookup("Date" & I, "[MyTable]", "Name ='" & MyName & "'")
        If myDate > MaxDate Then
            MaxDate = myDate
        End If
    Next I

End Function

By the way why do you have multiple columns for the date.

A better table structure would be to have only one column for the date.

Then you would be able to achieve what you want with a simple query.

 

[CT Witter]

Or you could use these functions from neatcode from MS KB

Place below in module

Function Max2(A As Variant, B As Variant) As Variant
'
' Returns the greater of 2 values
'
  Max2 = IIf(A > B, A, B)
End Function

Function Max3(A As Variant, B As Variant, C As Variant) As Variant
'
' Returns the greatest of 3 values
'
  If A > B Then Max3 = Max2(A, C) Else Max3 = Max2(B, C)
End Function

Call in your query as Max3([Date1],[Date2],[Date3])

HTH,
CT