Select Max date from three different columns

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have three date columns in a table and I want to select the max date of the
three.

i.e. table:

ID date1 date2 date3
1 1/1/2000 1/1/2001 1/1/2002
2 1/1/1999 1/1/2003 1/1/2000
3 1/1/1998 1/1/1995 1/1/1988

Desired result:

ID Maxdate
1 1/1/2002
2 1/1/2003
3 1/1/1998
 
Try using nested IIFs.
MaxDate: IIF([date1]> [date2], IIF([date1]> [date3], [date1],
IIF([date2]>[date3], [date2], [date3])))

Check 1>2 check 1>3, 1 is max, check 2>3, 2, then 3
 
You can use nested IIf() expressions, but it's messy with nulls.

Grab the MaxOfList() function from here:
MinOfList(), MaxOfList(): Get the min/max of a list of values
at:
http://allenbrowne.com/func-09.html

You can then type this into the Field row in query design:
MaxOfList([date1], [date2], [date3])

The article also points out that it would probably be better to have a
related table to store the dates, where you have several related records for
each ID rather than 3 fields in the same table storing similar kinds of
data.
 
Back
Top