>>>>>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.