Solved

Parameter for Top clause in a query

Posted on 2006-11-15
10
1,007 Views
Last Modified: 2008-01-09
Hello and thank you for reading my question.

Let's say that I have a query like this:


parameters intPercentage Integer;

SELECT TOP intPercentage Percent numGrades
FROM tblGrades
ORDER BY numGrades DESC;

The problem seems to be that I can't use a parameter for this purpose.

Apart from using the QueryDef() to modify the query, is there some way to accomplish this?

FtB







0
Comment
Question by:fritz_the_blank
  • 5
  • 5
10 Comments
 
LVL 58

Accepted Solution

by:
harfang earned 500 total points
ID: 17952776
Hello fritz_the_blank

I'm sorry to be the one. No.

You will need some (simple) coding to accomplish this, or devise another method based on your actual data, which can be very tricky.

(°v°)
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17957816
I was afraid that you were going to say that. Sometimes "it can't be done" is the correct answer.

My next thought is something like this:

objCommand.CommandText="SELECT Top 50 % FROM qrySomeQuery;"
objCommand.Execute

Will that work? Will the sort order in the qrySomeQuery still function?


If I take this approach, I can clearly pass the parameter like:


objCommand.CommandText="SELECT Top "  & numPercentage & "% FROM qrySomeQuery;"


FtB
0
 
LVL 58

Expert Comment

by:harfang
ID: 17959379
Yes. You need to create the query from VB and then use it through one of many database objects: DAO QueryDef, DAO OpenRecordset, ADO Execute, etc.

I'm not quite certain about sort-order propagation in the JetEngine. Often, the optimizer will drop all order clauses in the subqueries or base tables. It's always safer to include them in the top-levle query:

    .Execute "SELECT Top " & numPerc & "% FROM qryAny ORDER BY safe, n, easy"

Besides, it makes it easier to read and maintain, and doesn't loose more than a millisecond in parsing.

Cheers!
(°v°)
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17959639
There is that but I would prefer not having to create an additional WHERE clause for each of the queries.

Sometimes I don't much like Access......

FtB
0
 
LVL 58

Expert Comment

by:harfang
ID: 17959808
Part of it is common to all DB engines, and SQL is really a strange and uncomfortable language... As for the rest, can't really help. Some like it, others don't. I like it for personal reasons: after 6 years of pen-and-paper SQL, Access was the first "live" SQL engine I could play with and not be billed computer time...
(^v°)
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17959938
One last thing before we close this out. Is there no way to use an EXEC() or some such to interpret the parameter or the whole SQL statement?

FtB
0
 
LVL 58

Expert Comment

by:harfang
ID: 17960355
Well, the whole SQL needs to go to the JetEngine, using one of .Execute, .OpenRecordset, etc. As for the parameters, you can have the JetEngine precompute the query and extract them, yes.

In DAO, this can be done with the QueryDef object's .Parameters collection (there is no other way, I think).

In ADO, the command object can do it for you:

    With New ADODB.Command
        .ActiveConnection = CurrentProject.Connection
        .CommandText = strSql
        .Parameters.Refresh
        Debug.Print .Parameters.Count
        .Parameters(0) = #4/1/2000#
        ' etc...
    End With

I suppose you can in some cases examine the parameter names, and if they are in the form: Forms!fmnuReports!cboCountry or the like, use Eval() to get the correct value...

Is that what you were after?
(°v°)
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17966843
I can pass the parameters without issue, like this for example:

parameters intPercentage Integer;

SELECT numGrades
FROM tblGrades
WHERE numGrades < intPercentage
ORDER BY numGrades DESC;


The issue is that I can't use a peremter in the top clause.....
0
 
LVL 58

Expert Comment

by:harfang
ID: 17969729
I know that, we have covered it before. But in your last comment, you asked how to "interpret the parameter", so I thought you needed some way to access them to attempt an Eval() -- for controls on forms for example -- or to analyse them yourself. Could you rephrase your question?
(°v°)
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17989038
I think that you had it right when you said it couldn't be done.

Quite simply, can one pass a parameter as part of the SELECT TOP prmSomeParameter Percent * FROM....

Access always chokes on this.

The way that I am doing it now in the absence of a better solution is:

objCommand.CommandText = "SELECT TOP " & intPercentage " * FROM qrySomeQuery;"

FtB
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now