Need help with IIF statement

[LynnEsther]

I am struggling with an IIF statement and am wondering if anyone can help. I have four date fields: Received_Date, Install_Date, Inventory_Date, Last_Logon_Date. I am writing a query and need to create a fifth field called Latest_Date, which will take the most recent of the four dates listed above. I can write the IIF statement just fine when dealing with the latest of two dates, but add in the other two dates and I'm stumped. Thanks in advance.

 

[Atomic Shrimp]

It's possible to do this with an IIF, but it's going to be horribly nested and repetitious - for example, finding the earliest of two is:

IIF([date1] < [date2], [Date1], [date2])

Finding the earliest of three is:

IIF([date3] <(IIF([date1] < [date2], [Date1], [date2])), [Date3], (IIF([date1] < [date2], [Date1], [date2])))

Finding the earliest of four is:

IIF([Date4] < (IIF([date3] <(IIF([date1] < [date2], [Date1], [date2])), [Date3], (IIF([date1] < [date2], [Date1], [date2])))), [Date4], (IIF([date3] <(IIF([date1] < [date2], [Date1], [date2])), [Date3], (IIF([date1] < [date2], [Date1], [date2])))))

 

[Atomic Shrimp]

By way of explanation, I constructed those like this (colour coded for hopeful clarity):

1. Start with a two-part IIF:

IIF(A<B,A,B)

2. Construct a template for the third part:

IIF(C<(something),C,(something) )

3. Replace 'something' with the (two-part) expression from step 1 (which will always be the lowest value so far) so:

IIF(C<(IIF(A<B,A,B)),C,(IIF(A<B,A,B)) )

4. Construct a template for the fourth part:

IIF(D<(something),D, (something))

5. Replace 'something' with the (three-part) expression from step 3 (which again, should be the lowest value so far) so:

IIF(D<(IIF(C<(IIF(A<B,A,B)),C,(IIF(A<B,A,B)) )),D, (IIF(C<(IIF(A<B,A,B)),C,(IIF(A<B,A,B)) )))