Select Top N Using a Parameter

[instructorgirl]

Is there any way to set up a parameter in a query to prompt a user to enter the Top N value they want to see? I know how to select the Top N value from the tool bar, but sometimes the user may want to change that from top 10 to top 15. Can this be done? Any ideas? Thanks!

 

[lupins46]

You cannot use a parameter field for the N value.
If you want to make the TopN flexible then you have to move it into code. You first need to create a 'dummy' query in the database window which can be used to hold the sql statement you create in code.
I have assumed that this query is called 'qryDummy' in the following code example. Obviously you need to change the sql statement to your own.
Run this code from a button click event.

Sub vartopn()

Dim db As Database
Dim qdf As DAO.QueryDef
Dim strsql, howmany
Set db = CurrentDb
Set qdf = db.QueryDefs("qryDummy")

howmany = InputBox("How many values to report?")

strsql = "SELECT TOP " & howmany & " Orders.OrderID, Orders.OrderDate, Orders.Freight"
strsql = strsql & " FROM Orders ORDER BY Orders.Freight DESC;"
qdf.SQL = strsql
DoCmd.OpenQuery ("qryDummy")
Set qdf = Nothing
Set db = Nothing

End Sub

 

[instructorgirl]

Thank you for your response. I am using the suggested code, but I am getting an error message that reads:

"Compile error User defined type not defined."

It's giving me the error message on:

Dim db as Database

Do I have a different version of an object library? I am using Access XP.

 

[PHV]

You have to reference the Microsoft DAO 3.x Object Library.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[instructorgirl]

This works great! Thanks for your help!