Latest date out of 3 date columns

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
Asatoma SadgamayaAnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
That could be:

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

Open in new window

Asatoma SadgamayaAnalystAuthor 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
Bill PrewIT / Software Engineering ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

Bill PrewIT / Software Engineering ConsultantCommented:
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
Asatoma SadgamayaAnalystAuthor Commented:
Thank you Bill, that worked for me.
Bill PrewIT / Software Engineering ConsultantCommented:
Great, glad that helped.


»bp
Asatoma SadgamayaAnalystAuthor 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 ConsultantCommented:
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
Asatoma SadgamayaAnalystAuthor Commented:
Thanks Bill
Dale FyeOwner, Dev-Soln LLCCommented:
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.
Gustav BrockCIOCommented:
your formula does not work if one value is null

That you didn't mention.
Dale FyeOwner, Dev-Soln LLCCommented:
That is why I use the function, it ignores NULL values passed to it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.