Solved

Parameter for Top clause in a query

Posted on 2006-11-15
10
1,019 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

920 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

12 Experts available now in Live!

Get 1:1 Help Now