How to find the latest date?

[David Ball]

Hi,

If I have four date Fields in a query, Astart, Bstart, Cstart, and Dstart and want to have a calculated field to find the latest date for each record how would I do that?
I have tried things like:

LatestDate: MAX(Astart, Bstart, Cstart, Dstart)



Thank you very much

Dave

 

[namliam]

2 Ways of doing that, unless you want to rethink your table structure...


1)
Use IIF... or a UDF
IIf(Astart > Bstart, ... etc)
Gets pretty involved fast if you want to test off all the different options

2)
Use a Union query, something like

Select YourKeyField, "A" as FieldName, AStart as startdate from yourtable
UNION
Select YourKeyField, "B" as FieldName, BStart from yourtable
UNION
Select YourKeyField, "C" as FieldName, CStart from yourtable
Union
Select YourKeyField, "D" as FieldName, DStart from yourtable

Now save this query as qryAllStartDates, make a second query

Select Yourkeyfield, fieldname, max(startdate) from qryAllStartDates
group by Yourkeyfield, fieldname

 

[vbaInet]

1)
Use IIF... or a UDF
IIf(Astart > Bstart, ... etc)
Gets pretty involved fast if you want to test off all the different options

According to namliam's first suggestion:

Public Function MaxDate(ParamArray Values() As Variant) As Date
    Dim dteMax As Date
    Dim i      As Integer
    
    For i = LBound(Values) To UBound(Values)
        If Values(i) > dteMax Then
            dteMax = Values(i)
        End If
    Next
    
    MaxDate = dteMax
End Function