Max Field Value with Criteria

[JohnV]

I have a field called import file date. I want to get the
Maximum date of the field that is less than a specified
date.
Import File Date
8/25/2003
9/1/2003
9/5/2003
9/12/2003
9/16/2003
9/23/2003
9/24/2003
10/1/2003
10/3/2003
10/8/2003

for example, if I specify the date 10/1/2003 then I want
to pull 9/23/2003. If I specify the date 9/16/2003 then I
want to pull 9/12/2003. When I query for max date it will
not take a criteria date. If I use a criteria I can pull
all the dates. I would like to find the most efficient
way to get the maximum value that is less than the
specified date. The table is currently small, roughly 10K
records, but can grow by 1,000 records everytime I run an
import.

Any help will be appreciated.
Thanks,
JohnV

 

[Tim Ferguson]

I have a field called import file date. I want to get the
Maximum date of the field that is less than a specified
date.

parameters SpecifiedDate DATETIME;
select max(SomeDate)
from sometable
where somedate < SpecifiedDate;


HTH


Tim F