Top Query Revealed: Parameters

Top Query Revealed: Parameters

This is the fifth and last in a series on the Top Query. In previous posts, I've discussed Simple Top Queries, Aggregate and Grouping Top Queries, Problem of Ties in a Top Query and Finding Random Records.

Create a Parameter for Top Value?

One question which often comes up with regard to Top queries is if you can supply the TOP predicate as a parameter like you can with Where criteria. Unfortunately, the answer is you can't, at least not with native SQL. You can, however, use code to modify your Top query programmatically.

To do this, we need to create a subroutine called TopParameter in a General Module. Of course, I could hard code it for a specific query, but it would be far more useful to have code that will modify any Top query that I supply it. So my code will include a QueryName argument that passes in the name of the query to be modified.

Sub TopParameter(QueryName As String)
On Error GoTo Err_TopParameter

Next, I'll declare some object and scalar variables.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strTopVal As String
Dim strSQLTemp As String
Dim StartPos As Integer

Then I need to open the current database and set the database object variable (db) to it. The QueryDefs method of the database object lets me open the query definition of the Top query I want to modify and set it to a querydef variable (qdf).

Set db = CurrentDb
Set qdf = db.QueryDefs(QueryName)

The SQL property of the QueryDef object reads the SQL statement of the query into a string variable so I can modify it.

strSQLTemp = qdf.SQL

Since every Top query has the TOP predicate with a space before and after it, I can find the position of the first space after the existing Top value. I need to do this because I have to remove the existing Top value and replace it with the new value.

StartPos = InStr(strSQLTemp, " TOP ") + 5

It's a good idea to test whether the query is actually a Top query or not, which I can do like this:

If StartPos = 5 Then
MsgBox "Not a Top Query"
GoTo Exit_TopParameter
End If

Now I'll prompt the user for the new Top value with an inputbox.

strTopVal = InputBox("Enter TOP value:")

Next I'll read all the text after the old Top value into a variable. To do that, find the first space after the TOP predicate like this:

strSQLTemp = Mid(strSQLTemp, InStr(StartPos, strSQLTemp, " "))

Now I've got all the pieces needed to rebuild the SQL string with the inputted Top value.

strSQL = "SELECT TOP " & strTopVal & strSQLTemp

Lastly, I'll set the SQL property of the query to the new SQL string, which will save the query with the new Top value.

qdf.SQL = strSQL

To finish it off, I'll add the Exit_TopParameter label which I used earlier to exit the routine if it is not a Top query. In addition, I'll clean up the object variables and add error trapping.

Exit_TopParameter:
db.Close
qdf.Close
Set db = Nothing
Set qdf = Nothing
Exit Sub

Err_TopParameter:
MsgBox Err.Description
Resume Exit_TopParameter
End Sub

To call the Query, I need to first call this code, then open the query, like this:

Call TopParameter("MyTopQuery")
DoCmd.OpenQuery "MyTopQuery", acNormal, acEdit

And that's it. Every time the code is run, it prompts for a new Top value, modifies, and then opens the query.

A free sample illustrating this process (and, indeed, all the information in the Top Query Series) can be found here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=233.

.