Select top "n" values from a query with a parameter

[racquetballguy]

Hi

I wish to have a user enter a parameter into a form for the number of rows to use to do an average. This form is based upon a query.

SELECT TOP does not support parameters. Something like the top values box in the query page would be ideal.

Is there a way to select only the top "n" values from a query?

Thank You
Ivan

[PEB]

Hi Ivan,

Not sure about:
"SELECT TOP does not support parameters."

Maybe the pb is elsewhere!

:)

Hi

I wish to have a user enter a parameter into a form for the number of rows to use to do an average. This form is based upon a query.

SELECT TOP does not support parameters. Something like the top values box in the query page would be ideal.

Is there a way to select only the top "n" values from a query?

Thank You
Ivan

[DirkB]

You could built a select statement, or copy it from SQL view. The Select Top N will not support an imbedded parameter but you can do an inputbox to get the N and concat (&) into the sql query text. Then do DoCmd.RunSQL txtSQL where txtSQL contains the action query. Only works for an action query, make-table, update, delete, etc. A partial example follows

Dim txtPrompt as String, txtSQL as String, tmpIB as String
txtPrompt = "How many rows?"
tmpIB = InputBox(txtPro mpt, "Top X Rows", "5")

' force to 5 if less than 1
If Val(tmpIB) < 1 Then tmpIB = "5"

txtSQL = "SELECT TOP " & tmpIB
txtSQL = txtSQL & " rest of sql string"

DoCmd.RunSQL txtSQL

[Guy Moseley]

Hi
Have a look at the following:

http://www.fontstuff.c om/access/acctut17.htm

Alternatively this is the short version
First create a query, Access doesn't let you create an "empty" query so you'll have to make something up (anything will do).
Now create a dialogue Form to collect the variable in this case the value of "n".
You can add to this form command button which will give you an event to run the VBA from.
Now use VBA to modify the query you saved earlier.

 
Dim strSQL, strVar As String
 
strVar = ‘Dialogue Form Control’
strSQL = "SELECT TOP " & strVar & " rest of SQL "
 
CurrentDb.QueryDefs("QueryName").SQL = strSQL
 
 

And that should do you. If you want to you can now open another form / report based on this query.