Selecting a record closest match

[ChrisBeach]

Hi, I was wondering if anyone could help me. I have a table with about 10 fields.. one of these fields is a date field, I need to be able to have a date lets say 01/05/2005, plug that in, and have sql code find the record that is closest to matching that date so if you have

1/1/2005
1/2/2005
1/10/2005

if would select 1/2/2005

Any help is greatly appreciated. Thanks

-Chris Beach

 

[Golom]

Try

Select TOP 1 myTbl.DateField, 
Abs(DateDiff("d",myTbl.DateField, [Enter the Date])) As Days

From myTbl

Order by 2

Add other fields as necessary and adjust your "Order By" to sort on the computed field.

 

[PHV]

Golom, since when are TOP, DateDiff and [Enter the Date] parts of ANSI SQL ?

 

[carp]

SELECT * FROM your_table
WHERE ABS(your_date_field - your_specified_date) =
(SELECT MIN(ABS(your_date_field - your_specified_date))
FROM your_table);

Since this actually requires two queries, hopefully your table doesn't have too many rows in it!

 

[ChrisBeach]

Thanks alot for the help guys, got the query done, and the app setup on the users machine