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

[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

 

[Graham R Seach]

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]

Thanks Graham I will try this. What is "Watch out for newsreader
linewrap"?

 

[Graham R Seach]

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

 

[Jenny]

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

 

[Graham R Seach]

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