Top Query

[Secret Squirrel]

Is there a way to set a parameter for the "Top n" in a query? I want my users
to be able to enter a number and have it return the "top n" vendors.

 

[a a r o n . k e m p f]

Only with SQL Server can you have TOP N as a variable; and this only
came with SQL 2005

 

[John W. Vinson]

Is there a way to set a parameter for the "Top n" in a query? I want my users
to be able to enter a number and have it return the "top n" vendors.

Aaron's correct in this case - you can't parameterize the TOP value. The only
way to do so is to construct the SQL string in code.

 

[Coderz]

You can however create a temporary table and call a TOP N query using code.
ex:

sSql = "SELECT TOP " & intTop & " id, title FROM table1;"

to make a make query try using this code:

SELECT TOP 5 Table1.id, Table1.title INTO table1temp
FROM Table1;

Hope this help

http://www.sourcecodester.com - Download free source code

 

[Secret Squirrel]

I was able to figure it out thanks to your post. I created a SQL string and
then using that string created a query that I then used for my report. I
based the "Top N" on a textbox on my form so the users can enter a number
then fire the SQL string to create the query. Within my code I also delete
the query it creates so that the value that was last used doesn't remain.
Here's a sample of the code I'm using:

Private Function TopParameter()

Dim dbf As Database, qdf As QueryDef, strSQL As String, strSQL1 As String
Set dbs = CurrentDb

On Error Resume Next
DoCmd.DeleteObject acQuery, "ListTop"

strSQL = "SELECT TOP " & [Forms]![frmSelectVendors]![txtTopCount] & "
tblVendors.VendorID, tblVendors.VendorName,
qryPerformanceDetailExtCost.ExtCost"

Set dbf = DBEngine.Workspaces(0).Databases(0)
Set qdf = dbf.CreateQueryDef("ListTop", strSQL)


End Function

 

[John W. Vinson]

I was able to figure it out thanks to your post. I created a SQL string and
then using that string created a query that I then used for my report. I
based the "Top N" on a textbox on my form so the users can enter a number
then fire the SQL string to create the query. Within my code I also delete
the query it creates so that the value that was last used doesn't remain.
Here's a sample of the code I'm using:

Private Function TopParameter()

Dim dbf As Database, qdf As QueryDef, strSQL As String, strSQL1 As String
Set dbs = CurrentDb

On Error Resume Next
DoCmd.DeleteObject acQuery, "ListTop"

strSQL = "SELECT TOP " & [Forms]![frmSelectVendors]![txtTopCount] & "
tblVendors.VendorID, tblVendors.VendorName,
qryPerformanceDetailExtCost.ExtCost"

Set dbf = DBEngine.Workspaces(0).Databases(0)
Set qdf = dbf.CreateQueryDef("ListTop", strSQL)

That'll work. You don't actually need to create a Query object though - you
can set the Report's Recordsource property to the SQL string in the report's
Open event if you wish.

 

[a a r o n . k e m p f]

or of course-- you could move to SQL Server that natively supports a
parameter like

create procedure spGetTopNRecords
(
@N Integer
)
As
Select top (@N) *
From MyTable


so again-- you can either decide to use the built in functionality--
or you can subject your users to sub-optimal execution plans.

-Aaron

I was able to figure it out thanks to your post. I created a SQL string and
then using that string created a query that I then used for my report. I
based the "Top N" on a textbox on my form so the users can enter a number
then fire the SQL string to create the query. Within my code I also delete
the query it creates so that the value that was last used doesn't remain..
Here's a sample of the code I'm using:

Private Function TopParameter()

Dim dbf As Database, qdf As QueryDef, strSQL As String, strSQL1 As String
Set dbs = CurrentDb

On Error Resume Next
DoCmd.DeleteObject acQuery, "ListTop"

strSQL = "SELECT TOP " & [Forms]![frmSelectVendors]![txtTopCount] & "
tblVendors.VendorID, tblVendors.VendorName,
qryPerformanceDetailExtCost.ExtCost"

Set dbf = DBEngine.Workspaces(0).Databases(0)
Set qdf = dbf.CreateQueryDef("ListTop", strSQL)

That'll work. You don't actually need to create a Query object though - you
can set the Report's Recordsource property to the SQL string in the report's
Open event if you wish.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

 

[Secret Squirrel]

I assume I would need to leave my form open to pass the SQL string to the
reports recordsource when I open the report since the public function I
created is on the form. Correct? How would I call that string from the open
event this way?

I was able to figure it out thanks to your post. I created a SQL string and
then using that string created a query that I then used for my report. I
based the "Top N" on a textbox on my form so the users can enter a number
then fire the SQL string to create the query. Within my code I also delete
the query it creates so that the value that was last used doesn't remain.
Here's a sample of the code I'm using:

Private Function TopParameter()

Dim dbf As Database, qdf As QueryDef, strSQL As String, strSQL1 As String
Set dbs = CurrentDb

On Error Resume Next
DoCmd.DeleteObject acQuery, "ListTop"

strSQL = "SELECT TOP " & [Forms]![frmSelectVendors]![txtTopCount] & "
tblVendors.VendorID, tblVendors.VendorName,
qryPerformanceDetailExtCost.ExtCost"

Set dbf = DBEngine.Workspaces(0).Databases(0)
Set qdf = dbf.CreateQueryDef("ListTop", strSQL)

That'll work. You don't actually need to create a Query object though - you
can set the Report's Recordsource property to the SQL string in the report's
Open event if you wish.

 

[a a r o n . k e m p f]

create procedure spGetTopNRecords
(
@N Integer
)
As
Select top (@N) *
From MyTable

I assume I would need to leave my form open to pass the SQL string to the
reports recordsource when I open the report since the public function I
created is on the form. Correct? How would I call that string from the open
event this way?

On Sat, 27 Dec 2008 17:31:01 -0800, Secret Squirrel

I was able to figure it out thanks to your post. I created a SQL string and
then using that string created a query that I then used for my report.I
based the "Top N" on a textbox on my form so the users can enter a number
then fire the SQL string to create the query. Within my code I also delete
the query it creates so that the value that was last used doesn't remain.
Here's a sample of the code I'm using:
Private Function TopParameter()
Dim dbf As Database, qdf As QueryDef, strSQL As String, strSQL1 As String
Set dbs = CurrentDb
On Error Resume Next
DoCmd.DeleteObject acQuery, "ListTop"
strSQL = "SELECT TOP " & [Forms]![frmSelectVendors]![txtTopCount] & "
tblVendors.VendorID, tblVendors.VendorName,
qryPerformanceDetailExtCost.ExtCost"
Set dbf = DBEngine.Workspaces(0).Databases(0)
Set qdf = dbf.CreateQueryDef("ListTop", strSQL)

That'll work. You don't actually need to create a Query object though -you
can set the Report's Recordsource property to the SQL string in the report's
Open event if you wish.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

 

[John W. Vinson]

I assume I would need to leave my form open to pass the SQL string to the
reports recordsource when I open the report since the public function I
created is on the form. Correct? How would I call that string from the open
event this way?

You can actually open the criteria form in the Report's Open event. Open it in
Dialog mode so that code execution stops; the user can fill in the criteria.
Then put a command button on the form to set the form's Visible property to No
- this resumes the code and the report executes.

Then close the (hidden) form in the Report's Close event.

 

[Secret Squirrel]

Makes sense. I understand what you're saying. But how do I pass the SQL
string to the recordsource of the report?

 

[John W. Vinson]

Makes sense. I understand what you're saying. But how do I pass the SQL
string to the recordsource of the report?

There's no need to do so. The report's Recordsource is just a query
referencing the criteria form with parameters such as

=[Forms]![frmCrit]![controlname]

When frmCrit is opened, the user can enter criteria into controls on the form;
clicking the button will resume the opening of the report, and the query will
get its parameters from the form.

 

[a a r o n . k e m p f]

John;

Do you know how to read?
This guy is using Sql-Stringing to build a query.

How is he supposed to do that with a forms parameter?

Only with SQL Server can you write expressions in fields-- like in
Reporting Services.
With Access, you need to write mountains of VBA that cripple most
databases.

It's time for a 21st century toolset-- SQL Server is here; and it is
the worlds most popular database.

Just because you're too stupid to learn the 'worlds most popular
database' that doesn't mean that you can purposefully confusing
people.
ULS and Replication are not available in Access 2007 (format).

Screw you and your baby-sized databases.

Makes sense. I understand what you're saying. But how do I pass the SQL
string to the recordsource of the report?

"John W. Vinson" wrote:

There's no need to do so. The report's Recordsource is just a query
referencing the criteria form with parameters such as

=[Forms]![frmCrit]![controlname]

When frmCrit is opened, the user can enter criteria into controls on the form;
clicking the button will resume the opening of the report, and the query will
get its parameters from the form.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -