Top n Query- Allow n to act as criteria where the user can enter a number each time the query is run

[Nico80000]

I understand how to perform a "Top" query in Access by using the properties pane.


My question is if there is a different way of doing this where the top n records wasn't a static number but instead, it could be entered in a pop-up box in the same manner as a parameter.

I would love to have the ability to re-run the same Top query, which could be tied to a report, where I would not have to go into the back-end of the query, and manually change the properties pane each time I need a different amount of Top n records to populate my report.

Any suggestions are welcome, and if this is a functionality that simply doesn't exist in Access, please let me know.
My thought is that maybe this could be achieved through a macro, but I'm not very well-versed in macros so I could be completely wrong.

[Welshgasman]

Yes, you would just concatenate the top value into the rest of the sql.

[Micron]

Yes, if you think you'll never need to add/exclude a query field, simply get n from a form field or input box and run the sql in code.

If you might alter query design in the future it would be more complicated but still doable. The advantage would be that if you alter the query, you won't have to find and edit your code as well.

[Nico80000]

Could you provide an example of what the SQL code would look like?

[Nico80000]

@Welshgasman Could you provide an example of what the SQL code would look like?

[Welshgasman]

strSQL = "SELECT TOP " & YourInputNumber & " TableName.* FROM TableName
Debug.Print strSQL

Then use the strSQL as needed. The Debug is to make sure your/my syntax is correct.

YourInputNumber can come from an INPUT function or better still a form control.

[orange]

You might find this thread offers some insight.

[Micron]

I presume this is all about a SELECT sql statement and that matters since you cannot simply run one in code. You'd have to assign it to a query def, form, report or recordset so what the code looks like highly depends on all of that. If it is a SELECT statement, something like the following 'air code'

Private Sub SomeButton_Click()
Dim strSql As String
Dim n As Integer

strSql = "SELECT TOP x FROM..." '< use exactly x here
n = Inputbox "Enter value for top number of records.", "TOP VALUE FOR QUERY"
If n > 0 Then 
  Replace(strSql,"x",n)
Else
  Msgbox "Value must be greater than zero and not null"
  Exit Sub
End If
'now do something with the resulting sql as noted above

End Sub