Solved

Parameter for Top clause in a query

Posted on 2006-11-15
10
1,043 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

695 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