Parameter for Top clause in a query

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







LVL 46
fritz_the_blankAsked:
Who is Participating?
 
harfangCommented:
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
 
fritz_the_blankAuthor Commented:
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
 
harfangCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
fritz_the_blankAuthor Commented:
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
 
harfangCommented:
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
 
fritz_the_blankAuthor Commented:
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
 
harfangCommented:
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
 
fritz_the_blankAuthor Commented:
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
 
harfangCommented:
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
 
fritz_the_blankAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.