DLookup Top 1

[penfold1992]

I'm looking for a way to find:

["ID"] in Requests
where the criteria is the Max ["Created Date"] (the entry with the most recent date)

is this possible with DLookup or should it be done using SQL?

 

[namliam]

I would avoid DLookup whenever possible... go go SQL

 

[penfold1992]

I would avoid DLookup whenever possible... go go SQL

SQLstr = "SELECT TOP 1 [ID] FROM [Backup] ORDER BY [Created Date] Desc"
    
Set rs = dbs.OpenRecordset(SQLstr)
    
    Debug.Print rs.Fields(0)

This is what ive tried... but I cant seem to get the correct output I expect..

 

[pr2-eugin]

How about?

strSQL = "SELECT TOP 1 Backup.ID FROM Backup " & _
         "WHERE Backup.[Created Date] IN (SELECT Max(Backup.[Created Date]) AS MaxOfCreated FROM Backup);"
Set rs = dbs.OpenRecordset(SQLstr)
Debug.Print rs.Fields(0)