Most recent date prior to test date.

  • Thread starter Thread starter Skip
  • Start date Start date
S

Skip

I sometimes have to select a record who's date field is the most recent,
prior to a target date. Say I have 6 dates as shown below, the records are
entered in this sequence and the field type is [Date/Time].

Starting Date

10/17/2009

7/11/2009

6/29/2009

6/8/2008

9/4/2007



Say my query needs to select the record that is most recent prior to
10/01/2009. It should select the record with the date 7/11/2009. I always
seem to have trouble with this.



This doesn't return anything(?)

========================================

SELECT Max(x.[Starting Date]) AS [MaxOfStarting Date]
FROM x
HAVING (((Max(x.[Starting Date]))<#10/1/2009#))
ORDER BY Max(x.[Starting Date]);


This returns 9/4/2007. The last record in the table. This apparently ignores
the ORDER BY clause.

=========================================

SELECT Last(x.[Starting Date]) AS [LastOfStarting Date]
FROM x
HAVING (((Last(x.[Starting Date]))<#10/1/2009#))
ORDER BY Last(x.[Starting Date]);


This returns ALL records prior to 10/1/2009. Though the last one is the
correct one.

==========================================

SELECT x.[Starting Date]
FROM x
WHERE (((x.[Starting Date])<#10/1/2009#))
ORDER BY x.[Starting Date];


I usually place the above in a sub-query to get the correct date. But there
must be a more elegant method?



Thanks for reading my post.
 
smartin said:
Skip said:
I sometimes have to select a record who's date field is the most recent,
prior to a target date. Say I have 6 dates as shown below, the records
are entered in this sequence and the field type is [Date/Time].

Starting Date

10/17/2009

7/11/2009

6/29/2009

6/8/2008

9/4/2007



Say my query needs to select the record that is most recent prior to
10/01/2009. It should select the record with the date 7/11/2009. I always
seem to have trouble with this.



This doesn't return anything(?)

========================================

SELECT Max(x.[Starting Date]) AS [MaxOfStarting Date]
FROM x
HAVING (((Max(x.[Starting Date]))<#10/1/2009#))
ORDER BY Max(x.[Starting Date]);


This returns 9/4/2007. The last record in the table. This apparently
ignores the ORDER BY clause.

=========================================

SELECT Last(x.[Starting Date]) AS [LastOfStarting Date]
FROM x
HAVING (((Last(x.[Starting Date]))<#10/1/2009#))
ORDER BY Last(x.[Starting Date]);


This returns ALL records prior to 10/1/2009. Though the last one is the
correct one.

==========================================

SELECT x.[Starting Date]
FROM x
WHERE (((x.[Starting Date])<#10/1/2009#))
ORDER BY x.[Starting Date];


I usually place the above in a sub-query to get the correct date. But
there must be a more elegant method?



Thanks for reading my post.
Click to expand...

How about

SELECT max(x.start)
from x
where x.start < #10/01/2009#
Click to expand...

That sounds like a good idea. I will give it a try. Thanks.
 
Back
Top