Displaying # records around 1 record (alphabetically)

[Guest]

Is there a way that I can run a query to show a certain number or records
that are alphabetically above and below another record.

Thanks

 

[John Spencer]

Here is a sample of a query that would do that. It returned 5 records 2 on
either side of the Missing References. It may return more than that in case
of ties in the ranking - that will occur if there are duplicate values in
the field(s) you are using to create the ranking. This will be slow with
large recordsets (Say in the tens of thousands).

SELECT FAQ.fSubject
FROM FAQ
WHERE ((((Select Count(*)
FROM FAQ as F1
WHERE F1.fSubject < FAQ.fSubject))
Between (Select Count(*)
FROM FAQ as F2
WHERE f2.fSubject < "Missing References")-2
And (SELECT Count(*)
FROM FAQ as F2
WHERE F2.fSubject< "missing References")+2))
ORDER BY FAQ.fSubject;