Latest date out of 3 date columns

Asatoma Sadgamaya
Asatoma Sadgamaya used Ask the Experts™
on
Hi

I have 3 columns with dates on MS Access query. I need find out latest date out of these 3 on my new column.

Please let me know.

Thank you
A
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That could be:

LatestDate: IIf([Date1] > [Date2] And [Date1] > [Date3], [Date1], IIf([Date2] > [Date3], [Date2], [Date3]))

Open in new window

Author

Commented:
Thanks for your reply Gustav,

I could see your formula does not work if one value is null I am afraid. It returns null.

Ta
A
IT / Software Engineering Consultant
Top Expert 2016
Commented:
Try coding around the nulls, perhaps like:

LatestDate: IIf(Nz([Date1],0) > Nz([Date2],0) And Nz([Date1],0) > Nz([Date3],0), [Date1], IIf(Nz([Date2],0) > Nz([Date3],0), [Date2], [Date3]))

Open in new window



»bp
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
For what it's worth, I only got a NULL as the result of that calculated column if all three columns were NULL.  If just one or two were it still returned the largest date of the others.  Based on that you might be able to just do:

LatestDate: Nz(IIf([Date1] > [Date2] And [Date1] > [Date3], [Date1], IIf([Date2] > [Date3], [Date2], [Date3])),0)

Open in new window



»bp

Author

Commented:
Thank you Bill, that worked for me.
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Great, glad that helped.


»bp

Author

Commented:
Bill, what is the difference between your 1st and 2nd formulas. I did not see the 2nd one, I just spotted now.

Can I still use 1st?

Thank you
A
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Try them both, if they both solve your problem for all cases where NULL shows up then I'd go with the second, it's a bit more efficient.


»bp

Author

Commented:
Thanks Bill
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I use a function, which looks like:
Public Function fnMax(ParamArray ValList() As Variant) As Variant

   Dim intLoop As Integer
   Dim myVal As Variant
   
   For intLoop = LBound(ValList) To UBound(ValList)
      If Not IsNull(ValList(intLoop)) Then
         If IsEmpty(myVal) Then
            myVal = ValList(intLoop)
         ElseIf ValList(intLoop) > myVal Then
            myVal = ValList(intLoop)
         End If
      End If
   Next
   fnMax = myVal
   
End Function

Open in new window

With this function, you would simply write:

LatestDAte: fnMax([Date1], [Date2], [Date3])

It accepts a parameter array, so you can pass as few or as many values or fields into it as you want.
You can use it with dates, numbers, even strings to find the maximum, although I've never used it with strings.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
your formula does not work if one value is null

That you didn't mention.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
That is why I use the function, it ignores NULL values passed to it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial