?
Solved

Parameter for Top clause in a query

Posted on 2006-11-15
10
Medium Priority
?
1,056 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 2000 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
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!

 
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 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