Top N query where N is a parameter

[SunWuKung]

Hi all,
I have been struggling for quite some time now with creating a query that would only return the records with the top N values where N is a user supplied parameter. I know I can use the Select Top 5 ... syntax but I have been unable to find a way that would enable users to enter the 5 as a parameter.
As I am not a properly educated programmer I used trial and error and so far I only received error with this one.
I tried to enter it as a SQL parameter with no luck, tried to modify the Querydef from code but it seems the TopValues property can't be modified from code, tried to use the MaxRecords but it seems it only applies to ODBC. And of course searched for a solution on a number of forums.

I would be grateful for any help.
Many thanks.
SWK

 

[Jack Cowley]

Dim db As DAO.Database
Dim QD As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete ("My_Query")
On Error GoTo 0

strSQL = "SELECT TOP " & MyVar & " MyTable.ID, MyTable.LName FROM MyTable ORDER BY MyTable.LName;"

Set QD = db.CreateQueryDef("My_Query", strSQL)

DoCmd.OpenQuery "My_Query"

Replace MyVar with field on form or other variable....


[This message has been edited by Jack Cowley (edited 04-07-2002).]

 

[SunWuKung]

Many thanks Jack, it did solve my problem.
SWK