# Latest date out of 3 date columns

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.

Thank you
A
Comment
Watch Question

Do more with

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]))
``````

Commented:

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]))
``````

»bp
IT / 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)
``````

»bp

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

Commented:

»bp

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
IT / 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

Commented:
Thanks Bill
Owner, 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
``````
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.
Owner, 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