asked on

How to find the Maximum Date from Columns

I am trying to pick the correct date (Result Date) when comparing two dates in an Access query. I am able to determine which date to pick first in an IIf statement if one of the date fields is Null by the following function:

=iif(IsNull(Second Date), First Date)

but I haven't been able to figure out how to add the "then" to pick the Maximum of the remaining dates. So my data currently looks like (see attachment for clearer view of columns):

First Date        Second Date Result Date
3/19/2012             3/19/2012
10/14/2014      10/14/2014
8/13/2014      11/24/2014
3/18/2014             3/18/2014
6/9/2014             6/9/2014
11/4/2013             11/4/2013
6/20/2014      12/11/2014
7/14/2014      11/22/2014
9/10/2013      12/11/2014
2/6/2014             2/6/2014
7/25/2012             7/25/2012

I need guidance on how to get the blanks in the Result Date filled in with the max of the two dates. Hope this makes sense. Thanks.
C--Users-E221037-Desktop-Capture.PNG

ASKER CERTIFIED SOLUTION

Simon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom Farrar

ASKER

Hi Simon - It is close, but for some reason the second option does not appear to be working. I am getting the minimum of the Second Date and the First Date. I need the Max. Am I looking at this wrong?

Simon

When you say Max, I assume you mean the most recent of the two dates?

Are your fields actually dates or text representations of dates?

I just fired up my laptop to test this and I get the correct results (the later date of the two or the only date if one is blank or empty result column if both dates were blank).

Please let me know the actual dates you are using to test this, and possibly try some extra test cases
e.g. 1/1/2014 v 12/31/2014

Rey Obrero (Capricorn1)

try this

ResultDate: IIf([FirstDate]>Nz([SecondDate]),[FirstDate],[SecondDate])

Robert Sherman

Alternatively, especially if you need to do this in more than one query, you can put the below code into a module.

This will give you the max of any number of values, and can be put into your query as
ResultDate: max([FirstDate],[SecondDate])

Function max(ParamArray p()) As Variant

    Dim i As Integer
    
    max = p(0)
    
    For i = 1 To UBound(p)
        If max < Nz(p(i), "") Then
            max = p(i)
        End If
    Next
    
End Function

Open in new window

Tom Farrar

ASKER

Hi Simon - They are date-defined in the tables. Here is the actual function, and the result is shown in the attached.

LAST_USED_DT: IIf(Nz([Transaction_Dates_JDE]![LST_XFER_DT],#1/1/1900#)>Nz([ConversionDateImport Table]![LST_XFER_DT],#1/1/1900#),Nz([Transaction_Dates_JDE]![LST_USED_DT],Null),Nz([ConversionDateImport Table]![LST_USED_DT],Null))

Tom Farrar

ASKER

Forgot the file..
C--Users-E221037-Desktop-Capture.PNG

Nick67

I'd try this

=iif(nz([Second Date],0)>=nz([First Date,0]), [Second Date], First Date)
Nz() is an isnull and iif wrapped together

But what do you want to have happen if both dates are null

Nick67

These make little sense
Nz([Transaction_Dates_JDE]![LST_USED_DT],Null)
Nz([ConversionDateImport Table]![LST_USED_DT],Null)
There's little point in testing for and replacing null just to replace it with null

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom Farrar

ASKER

Thanks, Rey, that is what happens when I get to the end of the day. My mistake. Should always be [LST_USED_DT] for both tables. Now the functions are working. I am going home, but will be back to confirm in the morning. Thanks for everyone chipping in. Sorry for the error. - Reilly

Tom Farrar

ASKER

A lot of good input; thank you.

Tom Farrar

ASKER

Thank, Simon, for the immediate answer as it was important. Thanks, Rey, for the alternative solution, and pointing out my date error. I am sure the rest of you have provided good detail that I hope to look at closer as time permits. Thanks again. - Reilly