Power By 专否社区

Select the record of a given id with the highest timestamp using DAO

Go To StackoverFlow.com

0

How do I do the following using DAO on a recordset

SELECT TOP 1 * FROM foo WHERE id = 10 ORDER BY timestamp DESC

Using SetCurrentIndex you can only use one index it seems otherwise using id and timestamp and selecting the first one would work.

2012-05-21 08:53
by inquam

0

I am by no means sure of what you want.

Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDB

sSQL = "SELECT TOP 1 * FROM foo WHERE id = 10 ORDER BY timestamp DESC"
Set rs = db.OpenRecordset(sSQL)

Find does not work with all recordsets. This will work:

Set rs = CurrentDb.OpenRecordset("select * from table1")
rs.FindFirst "akey=1 and atext='b'"

If Not rs.EOF Then Debug.Print rs!AKey

This will not:

Set rs = CurrentDb.OpenRecordset("table1")
rs.FindFirst "akey=1 and atext='b'"
2012-05-21 11:32
by Fionnuala
I mean if I already have a recordset that I have to work with. Creating my own one is not a problem. But here I have a huge one in a huge system so I don't want to query the DB again to crate a new on. Instead I wonder how to perform this on the existing recordset using Seek or something similar - inquam 2012-05-21 14:03
Post some code - Fionnuala 2012-05-21 14:21
The need for code is kind of redundant I think. Imagine having a recordset of data. It's from a table that has at least id and timestamp in it. How do you perform a Seek() on it that would equal the above sql query - inquam 2012-05-21 15:41
The point of posting code is that I get to see what kind of recordset you have. The point of the code I posted is that it suggests that there is no reason for you to use seek or find when it is so much faster and easier to simply open a new recordset. I have added some notes - Fionnuala 2012-05-21 15:47
Ads