In a row (from a query)with five dates, I want to find the latest date 1

[KHerm]

I need to find the last close date in each row from a query. Each row has five close dates. Of those five, I want to add a column in the query with the latest date.

It seems to me I will need to do a sort of some kind, and I'm guessing also that's going to make me have to write a function. But if there's a way in the Access query, I'd sure rather do that!

I'm toying with this idea, but it seems quite cumbersome and is already giving me a headache:

IIf(date1>date2,date1,IIf(date1>date3,date1,IIf(date1>date4.....))

There have to be too many possibilities to do it this way.

Ken

 

[MichaelRed]

Never say never, but I do not see any way to do this w/ sql that is not a LOT more complex than a SIMPLE procedure.


This is the function call in the query grid

MyDate: basMaxDate([Date1],[Date2],[Date3],[Date4],[Date5])

This is the same, as copied from the SQL statement

basMaxDate([Date1],[Date2],[Date3],[Date4],[Date5]) AS MyDate

This is the function itself. Paste all of it into any General/public module (NOT in a form module

Public Function basMaxDate(Optional Date1 As Date, Optional Date2 As Date, _
                           Optional Date3 As Date, Optional Date4 As Date, _
                           Optional Date5 As Date) As Date

    Dim MyDate(5) As Date
    Dim Idx As Integer

    If (Not IsMissing(Date1)) Then
        MyDate(1) = Date1
    End If

    If (Not IsMissing(Date2)) Then
        MyDate(2) = Date2
    End If

    If (Not IsMissing(Date3)) Then
        MyDate(3) = Date3
    End If

    If (Not IsMissing(Date4)) Then
        MyDate(4) = Date4
    End If

    If (Not IsMissing(Date5)) Then
        MyDate(5) = Date5
    End If

    For Idx = 1 To UBound(MyDate) - 1
        If (MyDate(Idx) > MyDate(Idx + 1)) Then
            basMaxDate = MyDate(Idx)
         Else
            basMaxDate = MyDate(Idx + 1)
        End If
    Next Idx

End Function

This COULD easily be made much more flexible, and return the MAX of almost any collection, however if you are not quite comfortable w/ modules, it is probably easier to just make it specific to your stated use. It may be a gentler introduction to the use of modules/procedures.


MichaelRed
redmsp@erols.com

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

 

[KHerm]

Thank you sooooooooooo much, Michael! I actually had 6 dates to compare, so I made the changes, created the function and called it from the query. It worked that first time.

Seems to run fast enough, too.

You made my day!

Ken