473,322 Members | 1,405 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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
3 16351
PEB
1,418 Expert 1GB
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
DirkB
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
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

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

Similar topics

4
by: erich93063 | last post by:
I have a stored procedure which is performing a search against a "task" table. I would like to pass in a variable called @strAssignedTo which will be a comma delimeted list of employee ID's ie:...
2
by: Ellen Manning | last post by:
Using A2K. In my table I have the field "Grant" which can have a value or be null. I have a query that counts the number of records and has a Where clause on the Grant field. The query won't...
3
by: MX1 | last post by:
I'm ready to pull the hair out of my head. I have a query with a couple of parameters that I want to get from combo boxes on a form. One parameter is a date with a dynamically calculated year and...
3
by: cassandra.flowers | last post by:
Hi, I was wondering if it is possible (Using access) to have a query parameter as a drop down box rather than a text box? e.g. typing as criteria for a query produces a box with a text box...
10
by: Lau Lei Cheong | last post by:
Hello, I really need to use volatile System.Int64 for a .NET v1.1 program in C#. But the compiler complains "a volatile field can not be of type long". How to work around it? Or is there any...
3
by: eskelies | last post by:
Good day all...I have a combo box that selects numbers (ie. 1,2,3, etc.). What I am looking for is the combo box to have an "ALL" selection. Right now this is what I have for my SQL code: SELECT...
0
by: HomerS007 | last post by:
Hi, I'm using asp.net 2.0 and sql server 2000 for my first ever project. On one of the page in the application, I want to limit what the user can see based on his/her login. It's a page that...
1
by: bswanstrom | last post by:
Hi there, trying to execute a db2 sql statement from a command line from UNIX. Here is the command I'm running db2 export to /work/ftp/lt/sku_insert.csv of del select digits'('UPC_NO')', UPC,...
11
by: Alexander Vasilevsky | last post by:
Linq "into" and "let" equally??? http://www.alvas.net - Audio tools for C# and VB.Net developers
24
sueb
by: sueb | last post by:
I want to create a query for my Surgery database that asks for a Start Date and an End Date (easy), but also asks for a Surgeon using a drop-down list. This list already exists (the main database...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.