TopValues must be descending, but records must be shown ascending 2

[AlexMidd]

OK, this one is doing my head in. I am sure there must be an answer out there as it seems so elementary but I cannot get my head round it.

I have a VB app that accesses an Access database. I want to pick the last 10 records according to date/time. This requires me to set up a query using SQL that uses the TOP keyword. For this to work I must sort in DESCENDING order, otherwise it takes the FIRST ten records.

The problem is, the ten records that result from this need to be shown in ASCENDING order, i.e. earliest date first up to the last date in the recordset.

If I was doing this within Access it would be no problem, I would just do a query for the first bit then incorporate that into a second query (I think). However, I cannot do this in VB. I have a recordset that picks out the last 10 records but they are in descending order. I cannot work out how to do another recordset that picks the 10 from the first recordset and reorders them in ascending order.

I am sure it can be done quite easily but it escapes me at the moment. Anyone got any ideas? Ta. Have fun! )

Alex Middleton

 

[CajunCenturion]

You might try reversing the traversal loop - normally you would

RS.MoveFirst
Do While RS.EOF = False
<proc the Record>
RS.MoveNext
Loop

But you could also

RS.MoveLast
Do While RS.BOF = False
<Proc the Record>
RS.MovePrevious
Loop

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[AlexMidd]

Thanks for that. It is so simple I don't know why I didn't think of it. I have done (changed 4 lines of code in two loops) and it works a dream. You get a star! Have fun! )

Alex Middleton

 

[CCLINT]

Generally, you can write a SELECT statement like this using a Sub-Query:


SELECT *
FROM MyTable
WHERE MyTable.MyField IN
(SELECT TOP 10 MyTable.MyField
FROM MyTable
ORDER MyTable.MyField DESC)
ORDER BY MyTable.MyField ASC [/b][/i][/u]*******************************************************
_{General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}