Top Query

On Fri, 26 Dec 2008 17:52:02 -0800, Secret Squirrel
wrote:

>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.
--

John W. Vinson [MVP]
 >> Stay informed about: Top Query 

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" wrote in
message
> 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.
>
 >> Stay informed about: Top Query 

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" wrote:

> On Fri, 26 Dec 2008 17:52:02 -0800, Secret Squirrel
> wrote:
>
> >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.
> --
>
> John W. Vinson [MVP]
>
 >> Stay informed about: Top Query 

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

>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]
 >> Stay informed about: Top Query 

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






On Dec 27, 5:48 pm, John W. Vinson
wrote:
> On Sat, 27 Dec 2008 17:31:01 -0800, Secret Squirrel
>
>
>
>
>
> wrote:
> >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 -
 >> Stay informed about: Top Query 

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?

"John W. Vinson" wrote:

> On Sat, 27 Dec 2008 17:31:01 -0800, Secret Squirrel
> wrote:
>
> >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]
>
 >> Stay informed about: Top Query 

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





On Dec 29, 5:46 pm, Secret Squirrel
wrote:
> 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?
>
>
>
> "John W. Vinson" wrote:
> > On Sat, 27 Dec 2008 17:31:01 -0800, Secret Squirrel
> > wrote:
>
> > >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 -
 >> Stay informed about: Top Query 

On Mon, 29 Dec 2008 17:46:01 -0800, Secret Squirrel
wrote:

>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.
--

John W. Vinson [MVP]
 >> Stay informed about: Top Query 

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:

> On Mon, 29 Dec 2008 17:46:01 -0800, Secret Squirrel
> wrote:
>
> >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.
> --
>
> John W. Vinson [MVP]
>
 >> Stay informed about: Top Query 

On Tue, 30 Dec 2008 13:11:45 -0800, Secret Squirrel
wrote:

>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:
>
>> On Mon, 29 Dec 2008 17:46:01 -0800, Secret Squirrel
>> wrote:
>>
>> >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.
>> --
>>
>> John W. Vinson [MVP]
>>

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]
 >> Stay informed about: Top Query 

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.






On Dec 30, 4:35 pm, John W. Vinson
wrote:
> On Tue, 30 Dec 2008 13:11:45 -0800, Secret Squirrel
>
>
>
>
>
> wrote:
> >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:
>
> >> On Mon, 29 Dec 2008 17:46:01 -0800, Secret Squirrel
> >> wrote:
>
> >> >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.
> >> --
>
> >>              John W. Vinson [MVP]
>
> 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 -
 >> Stay informed about: Top Query