By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,385 Members | 847 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,385 IT Pros & Developers. It's quick & easy.

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

P: 20
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
Sep 28 '06 #1
Share this Question
Share on Google+
3 Replies


PEB
Expert 100+
P: 1,418
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
Sep 30 '06 #2

P: 1
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(txtPrompt, "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
Nov 27 '07 #3

P: 5
Hi
Have a look at the following:

http://www.fontstuff.com/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.


Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim strSQL, strVar As String
  3.  
  4. strVar = ‘Dialogue Form Control’
  5. strSQL = "SELECT TOP " & strVar & " rest of SQL "
  6.  
  7. CurrentDb.QueryDefs("QueryName").SQL = strSQL
  8.  
  9.  

And that should do you. If you want to you can now open another form / report based on this query.
Nov 29 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.