Max Date from Multiple Fields 3

[hovercraft]

Greetings,

I have 3 different date fields; date1, date2, date3.
Sometimes date3 is greater than date2 but sometimes date2 is greater than date 3 and sometimes date1 is greater than date3 etc... no real logic to how the fields were populated.

How could I query the 3 fields for max date per record?

Thanks in advance,
Hovercraft

 

[SgtJarrow]

Hi, hovercraft....here's a short and simple way if you always only have three fields. There may be some better ways, but I would do the following:

Copy/Paste the following code into a new module in your database...Save the Module with the name of basGetMaxDate

Public Function GetMaxDate(dteDate1 As Date, dteDate2 As Date, dteDate3 As Date) As Date

    Dim dteMaxDate As Date
    
    dteMaxDate = dteDate1
    If dteDate2 > dteMaxDate Then dteMaxDate = dteDate2
    If dteDate3 > dteMaxDate Then dteMaxDate = dteDate3
    
    GetMaxDate = dteMaxDate

End Function

Then in your query, put the following into the Field row of an empty column:

TheMax: GetMaxDate([date1],[date2],[date3])

Of course, change the date1, date2 and date3 to the names of your three date fields. And you can change TheMax to some other nicer name if you want - like TheMaxDate or MaxDate.

That should just about do it. If you need some explanation of what I did here, let me know.

Final note is that this works for three fields and three fields only. If you needed more fields, I think you can see how to adapt it.

Final Final note is that if you are storing dates such as this, you may not be using the best method to store your data - i.e. not fully normalized - depending on the data you are storing. You may want to search this site for some posts on normalization and see if thy might be able to help you out.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[dhookom]

I also think Robert deserves a star for such a thorough explaination as well as the good advice on normalization.

The only issue might be if any one of the three date fields is null. If there is a chance of no date value in any of the fields you might need to call the function like:
TheMax: GetMaxDate(Nz([date1],0),Nz([date2],0),Nz([date3],0))


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hovercraft]

Thanks mstrmage!

The entire application I'm working with is complete garbage. It's the evil anti-database.
A database conceived of...only in...the twilight zone.

one table has 72 fields and over 900,000 records.

 

[SgtJarrow]

Duane deserves a star from me for the addition of the NULL. I tend to forget about that as I try to not allow my DBs to contain them. Therefore I forget to include them when I give examples. Thanks.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[MichaelRed]

Ther are several similar 'aggregate' functions throughout these (Tek-Tips) fora. The "key" to these (at least the ones I generated) is the use of the ParamArray. I believe that you could easily find routines like the one below for a variety of generic 'aggregate' functions (Min, Max, Avg, Sum, etc) just by searching for the single keyword "ParamArray" and narrow the search by adding the appoopiate term (Min, Max, ... )

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

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

    'Sample Usage:
    '? basMaxVal(1, 5, 29, 3, 13.663)
    '29
    '? basMaxVal(9, 1, 5, 3, 13.663)
    '13.663

    '? basMaxVal(#1/1/06#, , NUll, #12/31/05#, Date(), DateAdd("d", Date(), -736)) _
     8/22/2007(which happens to be the the date this was run
    Dim Idx As Integer
    Dim MyMax As Variant

    If (UBound(varMyVals) < 0) Then
        Exit Function
     Else
        MyMax = varMyVals(0)
    End If

    For Idx = 0 To UBound(varMyVals())
        If (Not IsMissing(varMyVals(Idx))) Then
            If (varMyVals(Idx) > MyMaxn) Then
                MyMin = varMyVals(Idx)
            End If
        End If
    Next Idx

    basMinVal = MyMax

End Function

MichaelRed

 

[PWise]

i would run this query

Select idcolumn Max(DateColumn) As Max DateColumn
from(
select idcolumn ,date1 As DateColumn
from Tablename
union
select idcolumn ,date2 As DateColumn
from Tablename
union
select idcolumn ,date3 As DateColumn
from Tablename)Dt
group by dt.idcolumn