TopValues property

[Bob L]

I have a query that contains a random number field, then
sorts by this field. I only want to pull the top values.
In the query window I can type the number, but I want to
run the query through a form and have the user get a
prompt asking how many of the top values they want. How
can I do this?

 

[Roger Carlson]

You can't select a TOP value in a parameter like you can in the WHERE
clause. You will have to create your query programmatically, save and run
it. Something like this:

In the OnClick property of a button on your form, have the following code:

dim db as dao.database
dim qdf as dao.querydef
dim strSQL as String
set db = currentdb
strSQL = "Select TOP " & me!combo1 & " * FROM MyTable"
set qdf = db.CreateQueryDef ("MyQuery", strSQL)
DoCmd.OpenQuery "MyQuery", acNormal, acEdit

This code requires a reference to DAO and assumes the query is named MyQuery
and the combo is named Combo1.

On my website are several small sample database that illustrate this
process. Mostly they start with "CreateQueries".

 

[Bob L]

Roger, your code worked great. Thanks for the help. With
a few minor changes your code did exactly what I needed.
I took a quick look at your site and I am sure I will use
it in the future.