Choose the lastest of 2 dates for a calculation involving a third.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to calculate the length of time between the most recent of 2 dates & a
third date.

In other words, if we were to call the dates "Event1", "Event2", and
"TheBigEvent", I want to have the report choose the most recent date out of
"Event1" and "Event2", then figure out the length of time between that date
and "TheBigEvent".

Can this be done?

By the way, the folks on this board are SO helpful. I truly AM grateful for
all the assists!
 
Sue:

For this to work easily, it would be best to have some flag in your table to
indicate which records are not the "big event" for a specific group of
records. So as an example if you were storing data you might have a field
something like "EventFinal" as a boolean. Every event preceding the "big
event" would be marked with a 0 and the big event would be marked -1 (i.e.
True).

Then, it would be rather simple to create what you want using two queries.
The first query would be a top query, where it would be the top 1 based on
the date with the date set to descending order (to get the last date) and
the "EventFinal" field specified as false. This would give you the last
date preceding the "big event" for each primary key related set of records.
Then in the second query, you'd add the first query (last preceding date
query) and select the same primary key related records by using an inner
join to those records where there was an EventFinal flag set as as True
(-1). Then simply use the DateDiff function to compare the date returned
from the Top (preceding date query) to the records returned where the
EventFinal was set to true.

If you don't have an "event final" flag to use, then it will be very had to
compare your dates because you done know if for a specific set of related
records (unless you are always only going to have three to compare) whether
you've got the "big event" to compare the other records to.

HTH
 
You didn't say what degree of granularity you want. Hours? Days? Weeks?
Assuming days, then you need a formula like the following.

DateDiff("d",IIF(Event1>Event2,Event1,Event2),TheBigEvent)

This assumes that Event1, Event2, and TheBigEvent are not null. If any one
of them is null then this may give you undesired results.
 
Back
Top