CODEWITHSUNDEEP
ms-access
Stephen Swiech
Stephen Swiech

Reputation: 1

Prompt for TOP value query

I am creating a query that will select a random list of 'x' number of entries. I would like to be able to change the number of values that the query returns each time I run the query.

Is it possible to prompt for a TOP return value rather than having to open the query design each time?

SELECT TOP 50 *
FROM [MyTableName]
ORDER BY Rnd([SomeNumber]);

Upvotes: 0

Views: 373

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123484

Unfortunately, it seems that the most obvious approach, namely...

PARAMETERS RowsToReturn Long;
SELECT TOP [RowsToReturn] *
FROM MyTableName
ORDER BY Rnd([SomeNumber]);

...won't work. Access complains that the SQL query is not valid.

The way I would approach it would be to create a little VBA function like the following, then create a macro that uses the RunCode action to invoke it

Public Function buildRandomTopN()
'
' assumes that the table has an AutoNumber primary key named [ID]
'
Dim rowsToReturn As Long, cdb As DAO.Database, qdf As DAO.QueryDef
Const queryName = "randomTopN"  ' existing QueryDef to update

On Error Resume Next
rowsToReturn = CLng(InputBox("Number of rows to return"))
If Err.Number = 0 Then
   On Error GoTo 0
   If rowsToReturn > 0 Then
      Set cdb = CurrentDb

      ' update the SQL statement for the query
      Set qdf = cdb.QueryDefs(queryName)
      qdf.SQL = "SELECT TOP " & rowsToReturn & " * FROM [MyTableName] " & _
          "ORDER BY Rnd([ID])"
      qdf.Close
      Set qdf = Nothing
      Set cdb = Nothing

      ' open the query
      DoCmd.OpenQuery queryName
   End If
End If
End Function

Upvotes: 1

Related Questions