>>>>>>Here is an interesting question. Is it possible to construct a sql-select statement that will return 3 adjacent records with the wanted keyfield in the middle? That is, a specified record WHERE keyfield = lnVal plus the record before and the record after. The 2 boundary records would be for the purpose of identifying the previous and next records for navigation purposes.
>>>>>
>>>>>You might be able to brute-force it by UNIONing 3 separate SELECTs, one each for the previous, current, and next rows. Rough pseudo-code:
>>>>>

>>>>>SELECT ;
>>>>>  ... ;
>>>>>  WHERE PKey = lnVal ;
>>>>>  UNION ;
>>>>>    SELECT TOP 1 ;
>>>>>    ...
>>>>>    WHERE PKey < lnVal ;
>>>>>    ORDER BY ... DESC
>>>>>  UNION ;
>>>>>    SELECT TOP 1 ;
>>>>>    ...
>>>>>    WHERE PKey > lnVal ;
>>>>>    ORDER BY ... ASC
>>>>>
>>>>>

>>>>>
>>>>>You'd need special handling when previous or next rows don't exist, or if another user INSERTs a new record that would be the "true" previous or next record while the first user is pondering her initial result set.
>>>>
>>>>Thanks Al. This might work if the base table sort was on the keyfield but if the sort was on something else like NAME we'd have a problem.
>>>
>>>Do you mean you want physical previous and next records ? Do you have deleted records in the table?
>>>
>>>Theoretically you can slightly modify Al's idea to use based on recno() select, but it would not work exactly if you have deleted records...
>>>
>>>UPDATE, Tried to figure this recno() based select and failed so far.
>>>
>>>I don't think we can use
>>>
>>>select * from ... where recno() between (one select) and (another select).
>>>
>>>Doesn't seem kosher to me.
>>
>>Would this work?
>>

>>select * from myTable where key = myKey ;
>>union ;
>>select top 1 * from myTable where name < (select Name from myTable where key=myKey) order by name descending;
>>select top 1 * from myTable where name > (select Name from myTable where key=myKey) order by name ascending
>>
>>

>
>However, it assumes that the base table was sorted on name. If it was not sorted at all, then we need to use recno() analogue.

Hi, I tried:

select * from people where person_id = 6  ;
union ;
select top 1 * from people where lastname < (select lastname from people where person_id=6) order by lastname descending 
union ;
select top 1 * from people where lastname > select lastname from people where person_id=6 order by lastname ascending

Each of the selects will work alone but not together with the UNION. The error says there is a missing required clause but I can't figure out what it is.