N-th record in a table/Query

[Rishi]

Hi,

how can i read the N-the record (N is being calculated) in a table or query?

Suppose I have 17000 records in a table (or query for that matter) .
and I would like to:
1. Sort the table on field Score
2. Get the field [Score] in the 13859-th record,

Thnx 4 your help!

 

[Paolo]

Hi Rishi,
this is the first thing that comes in my mind to do what you're asking

dim rec as DAO.recordset

end_move = 13859
Set rec = CurrentDb.OpenRecordset("select * from table order by score", _
dbOpenDynaset)
For i = 1 To end_move - 1
rec.MoveNext
Next i
MsgBox rec!score

this is aircode and you must test it.
HTH Paolo

 

[Ken Snell [MVP]]

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT * FROM TableName ORDER BY Score"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.EOF = False And rst.BOF = False Then
rst.Move 13589 - 1
Debug.Print rst.Fields(0).Name & " = " & rst.Fields(0).Value
Debug.Print rst.Fields(1).Name & " = " & rst.Fields(1).Value
Debug.Print rst.Fields(2).Name & " = " & rst.Fields(2).Value
Debug.Print rst.Fields(3).Name & " = " & rst.Fields(3).Value
End If
rst.Close
Set rst = Nothing
Set dbs = Nothing

 

[vanderghast]

SELECT TOP 1 score
FROM (
SELECT TOP 13860 score
FROM table
ORDER BY score ASC) AS x
ORDER BY x.score DESC



where the magical constant 13860 is simple 1 added to the position you want.

On the other hand, if there are LESS than 13859 records, it returns the last
one.


Vanderghast, Access MVP

 

[DaveT]

I would look at Access help for "recordset move".

Move is relative to starting position so code might look somehting like this:

Dim lngRecNum as Long

lngRecNum = 13859

open recordset r (however)
r.movefirst (kinda redundant since if r has any records you will be at start)
r.Move lngRecNum