Query second highest value

perlfan

Registered User.
Local time
Today, 07:16
Joined
May 26, 2009
Messages
192
Hi,

I am trying to get the ID of the second highest record. Unfortunately, the sql-result is always empty. What is wrong with my code? Thanks for help!

FRANK

Code:
Set db = CurrentDb
strSQL = "SELECT Min(QuoteID) FROM (SELECT TOP 2 * FROM Quotes ORDER BY QuoteID DESC)"
Set rs = db.OpenRecordset(strSQL)  ' Recordset öffnen
var = rs!QuoteID
 
The SQL is fine, I don't think you're properly referencing the value in the recordset though. Try this:

Code:
Set db = CurrentDb
strSQL = "SELECT Min(QuoteID) AS QID FROM (SELECT TOP 2 * FROM Quotes ORDER BY QuoteID DESC)"
Set rs = db.OpenRecordset(strSQL)  ' Recordset öffnen
var = rs!QID

This method explicitly names the Min value and then uses that name to reference it.
 
I'm surprised you don't get a run-time error with this!:confused:
If you deconstruct your SQL string, the embedded query returns a list of records with multiple fields (however many are in table 'Quotes'). The outer query is expecting a table name here.
You need to do something like this:
Code:
Set rs = CurrentDb.OpenRecordset("SELECT TOP 2 QuoteID FROM Quotes ORDER BY QuoteID DESC;")
rs.MoveLast
var = rs!QuoteID
it would be sensible to check rs.EOF before the MoveLast to make sure records were returned.
Is QuoteID the field whose value you are checking for maximum? If not, the query needs to be refined to give both the ID and the relevant field.
Try using the Query Wizard to create a query which gives the fields you want, then look at the SQL it generates - you can put that in your code.
 
Both ways work - so thanks a lot!!!!!!!! :)

FRANK
 

Users who are viewing this thread

Back
Top Bottom