Compare dates in same record to find most recent date

[LaurieW]

I have a table that has multiple date fields. I need to compare three of the date fields and return the most recent date. Here is a simplified example of my Main table (tblMain):

LName
FName
Date1
Date2
Date3

How can I find the most recent date between Date1, Date2 and Date3? I will also need to retrieve the related data such as LName and FName along with the most recent date for each record in the table.
Thanks for your help!

 

[Minty]

Normally fields called date 1 2 3 etc indicate a poor data structure that is now causing you a headache because comparing a variable number of fields that may or may not have data in them is not so simple...

What are you recording in these dates?

 

[CJ_London]

sounds like your data isn't normalised. You'll need something complex to compare each date with the other two

something like this

iif(date3>iif(date1>date2,date1,date2),date3,iif(date1>date2,date1))

 

[LaurieW]

In the example I gave I specified that it was SIMPLIFIED. Without getting into enormous detail, trust me to say that my table is fine. The dates are not related to each other in the way you are thinking.

 

[Minty]

Okay how many dates are we talking about here? 3? 10? do they all have data in them or are they null.

As you can see from CJ's example 3 is messy enough.

Humour us - tell us what they represent...

 

[LaurieW]

I need to compare THREE dates. I work in the housing industry and the dates represent different steps in a move out process. For example: Vacate Date, Lease Signed Date and Stipulation Date.

Yes, sometimes there may be a null value in one or more of these three date fields.

Thanks!

 

[plog]

...trust me to say that my table is fine

Never. Heard those pleas on this site before.

Regardless though, for your issue I'd just make a quick custom VBA function:

Public getFirstDate(in_Date1, in_Date2, in_Date3)

ret = in_Date1
    ' return value, by default is first date

' compare dates 2 & 3 to ret here and find out first one.  


getFirstDate=ret
End function

The beauty of this is that when you have to compare 4 or more dates, you just do it recursively:

FirstDate: getFirstDate(Date1, Date2, getFirstDate(Date3, Date4, Date5))

 

[CJ_London]

sometimes there may be a null value in one

so depending on how you want null values to be treated, wrap each date with the nz function (and supply an alternative date if required - e.g. 1/1/1900 or 31/12/2200)

the dates represent different steps in a move out process

having these in one record may seem the right way to go and certainly you would probably present the data in that format in a report or whatever, but it is still not necessarily correct for storing in a database.

 

[Minty]

If the fields are not going to change , for simplicity it would probably be easiest to write a small function if you need to do this frequently. I'm making the assumption you have a Unique ID field you can pass into the function?

Still think they probably aren't stored properly !

 

[LaurieW]

I tried using the first example:
iif([StipDate]>iif([Vacate]>[LeaseSigned],[Vacate],[LeaseSigned]),[StipDate],iif([Vacate]>[LeaseSigned],[Vacate]))

and I get an error about invalid syntax.

I have never used a VBA function in a query. How do you do that? I also don't understand the function as it is posted. How do you compare dates in VBA?

Yes I have a unique value in the field ID.

 

[Minty]

Just to point you in a slightly different direction...

Imagine a second table linked to your main table with just four fields An new unique EventID, your existing unique record ID, An event type Number and a date...

The date you are looking for would be the latest entry for the record ID. If you
You can add as many event types as you like but your query will still work.