Help with Selecting the most recent Date from three different Date Fields

  • Thread starter Thread starter Jenny
  • Start date Start date
J

Jenny

Thanks for your help in advance.

I have three different fields in the same table.

Event1
Event2
Event3

I need help with a Query that will select/show the most recent date
value of these three different Events.
My results Should/would/Could look like:

Event1 - Event2 - Event3 - MostRecentEvent
01/01/05 01/15/05 02/01/05 02/01/05
01/01/05 01/01/05
01/15/05 02/01/05 02/01/05
03/01/05 02/01/05 03/01/05
02/01/05 01/15/05 02/01/05

Also, is there a way to format the "MostRecentEvent" field as a Date
instead of a text?

Thanks again for your assistance. Jen
 
Jenny,

(Watch out for newsreader linewrap)

SELECT IIf(Event3 < IIf(Event1 < Event2, Event2, Event1), IIf(Event1 <
Event2, Event2, Event1), Event3) As MostRecentDate FROM MyTable

In the above example, MostRecentDate will be a DateTime datatype, as long as
Event1, Event2 and Event3 are all dates. If not, cast the expression as a
date:

SELECT CDate(IIf(Event3 < IIf(Event1 < Event2, Event2, Event1), IIf(Event1
< Event2, Event2, Event1), Event3)) As MostRecentDate FROM Table1

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Jenny,

Your newsreader can create several lines of text, where the code should be
all on one line.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Thanks

What happens if I only have one Date Field populated in one record?
If I only populate Event1 and do not populate Event2 or Event3 It does
not return the date for Event1.

Thanks agian for your help. Jen
 
Jenny,

In that case, use the Nz() function as shown below, which will return zero
(30/12/1899 12:00:00 AM) if the field is Null.

SELECT IIf(Nz(Event3,0) < IIf(Nz(Event1,0) < Nz(Event2,0), Nz(Event2,0),
Nz(Event1,0)), IIf(Nz(Event1,0) < Nz(Event2,0), Nz(Event2,0),
Nz(Event1,0)), Nz(Event3,0)) As MostRecentDate FROM MyTable

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Back
Top