Question about getting top 20 records but not the top 5

[neillovell]

Hello!
I know I can use
SELECT * Top 20 ORDER BY TheDateField
or something similar to get the top 20 records in a database, but how can I get records 5-15 ? I'm thinking if I had several forms or web pages each with select records only. Link when you search on ebay or google and you have those numbers at the bottom.

Thanks.

 

[Norris68]

You set the PageSize property of your recordset. When you open the recordset you will always get the first page of data anyway. You can navigate to a page by setting the AbsolutePage property. e.g.

Public Sub PageTest()
Dim R As ADODB.Recordset
Set R = New ADODB.Recordset
R.PageSize = 5 '5 items per page
R.Open "SELECT NumValue FROM tblBigNums ORDER BY NumValue DESC", CodeProject.Connection, adOpenStatic, adLockReadOnly
Debug.Print "There are " & R.PageCount & " pages"
R.AbsolutePage = 2
While R.AbsolutePage = 2 'Print all page 2 items
Debug.Print R("NumValue&quot.Value
R.MoveNext
Wend
R.Close
Set R = Nothing
End Sub

Look up the help on PageSize - there is a reasonable example. Good question by the way - I had wondered about this myself but never got around to looking it up.

 

[markgrizzle]

Use one query with the top value set to twenty. Use a second query with the first as the recordsource, order it in descending date order, and select the top fifteen.

I'm sure there has to be a simpler method; if anyone has a sql solution, I'd be very interested in seeing it.

 

[neillovell]

Wow thanks, very helpful both of you.

 

[n2756m]

SELECT COLUMNNAME TOP 20 WHERE COLUMNNAME NOT IN SELECT COLUMNNAME TOP 5

 

[TonyJollans]

Hi n2756m,

I like it and it will usually suit, but a small caveat. It won't work if, say, numbers 5 and 6 are duplicates on columnname.

Enjoy,
Tony