[AccessD] Determine Closest Two Records for a Given Value

Hi Josh

Use Abs() for this:

  SELECT TOP 1 
    ID,
    Distance
  FROM 
    tblDistance
  ORDER BY 
    ABS(5-[Distance]);

/gustav

>>> darsant at gmail.com 07-12-2005 18:02:27 >>>
OK, got a bit of a fun SQL problem.

I have a table that contains a distance floating point value. Given a
certain record, I need to find  the closest distance above and below
it in a seperate table (Same physical measurement system though, so I
can extrapolate the data. Is it possible to select the first record
above and below for a certain value?

IE:

I want data at distance 5.

However, the table has:
1
3.4
4.1
6.3

So, the SQL query should return records 4.1 and 6.3 so I can
extrapolate data from them.

If this isn't possible, what would be the next best thing? Maybe two
queries that did something such as
TOP 1 Distance>5 and TOP 1 Distance<5?

Thanks
--
Josh McFarlane