Select top N where N is a variable

S

stumac

I currently have a query which selects the top 10 numbers from a table (using
the RND function), I would like this to be changed so the user can set the
number of rows returned by entering a number between 1 and 10 into a text box
on a form and then pressing a button which runs the query. Is this possible?

I have very little knowledge of VB so if it is possible an example of the
code would be very much appreciated. Thanks in advance.

Stu
 
A

Allen Browne

The N in TOP N cannot be a variable/parameter.

The simplest solution is to create the SQL statement on the fly, e.g.:
Dim strSql As String
strSql = "SELECT TOP " & [SomeTextBox] & " Field1 FROM Table1;"
Me.RecordSource = strSql
 
S

stumac

Thnaks for your reply, I have put this in and something is certainly
happening, the only problem is I dont know how to get the results to now be
displayed (as i said i am new to VB), I have put the code under the onclick
event.

Stu

Allen Browne said:
The N in TOP N cannot be a variable/parameter.

The simplest solution is to create the SQL statement on the fly, e.g.:
Dim strSql As String
strSql = "SELECT TOP " & [SomeTextBox] & " Field1 FROM Table1;"
Me.RecordSource = strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

stumac said:
I currently have a query which selects the top 10 numbers from a table
(using
the RND function), I would like this to be changed so the user can set the
number of rows returned by entering a number between 1 and 10 into a text
box
on a form and then pressing a button which runs the query. Is this
possible?

I have very little knowledge of VB so if it is possible an example of the
code would be very much appreciated. Thanks in advance.

Stu
Click to expand...
Click to expand...
 
A

Allen Browne

What are you trying to do with this query?

If you want to show it in a form, set the RecordSource of the form as shown.
The OnClick event of a command button should be fine.

If you want to show this in a report, you must use the Open event of the
report to set its RecordSource. Any event after that is too late.

If you want the results to look like a query, use a form in Datasheet view.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

stumac said:
Thnaks for your reply, I have put this in and something is certainly
happening, the only problem is I dont know how to get the results to now
be
displayed (as i said i am new to VB), I have put the code under the
onclick
event.

Stu

Allen Browne said:
The N in TOP N cannot be a variable/parameter.

The simplest solution is to create the SQL statement on the fly, e.g.:
Dim strSql As String
strSql = "SELECT TOP " & [SomeTextBox] & " Field1 FROM Table1;"
Me.RecordSource = strSql

stumac said:
I currently have a query which selects the top 10 numbers from a table
(using
the RND function), I would like this to be changed so the user can set
the
number of rows returned by entering a number between 1 and 10 into a
text
box
on a form and then pressing a button which runs the query. Is this
possible?

I have very little knowledge of VB so if it is possible an example of
the
code would be very much appreciated. Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
S

stumac

Ahhh the penny drops, thanks very much for your help with this Allen, much
appreciated.



Allen Browne said:
What are you trying to do with this query?

If you want to show it in a form, set the RecordSource of the form as shown.
The OnClick event of a command button should be fine.

If you want to show this in a report, you must use the Open event of the
report to set its RecordSource. Any event after that is too late.

If you want the results to look like a query, use a form in Datasheet view.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

stumac said:
Thnaks for your reply, I have put this in and something is certainly
happening, the only problem is I dont know how to get the results to now
be
displayed (as i said i am new to VB), I have put the code under the
onclick
event.

Stu

Allen Browne said:
The N in TOP N cannot be a variable/parameter.

The simplest solution is to create the SQL statement on the fly, e.g.:
Dim strSql As String
strSql = "SELECT TOP " & [SomeTextBox] & " Field1 FROM Table1;"
Me.RecordSource = strSql

I currently have a query which selects the top 10 numbers from a table
(using
the RND function), I would like this to be changed so the user can set
the
number of rows returned by entering a number between 1 and 10 into a
text
box
on a form and then pressing a button which runs the query. Is this
possible?

I have very little knowledge of VB so if it is possible an example of
the
code would be very much appreciated. Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Top