"SELECT TOP X" where "X" comes from an input box

[Bril]

Hey everyone,

I'm trying to create a query that shows the top X number of, in my case, royalty payers in our royalty database. I know I can specify the number in the design view of the query, but what I would love to do is have the user input a number and have that number be how many records are shown (i.e. top 2, top 10, top 5, etc.). I have the dialog box pop up from the Access parameters, but how do I get the SELECT TOP command to use that input number?

PARAMETERS [How many records?] Short;
SELECT TOP 5 [Royalities Y2013].F1, [Royalities Y2013].F38, [Royalities Y2013].F39, [Royalities Y2013].F40
FROM [Royalities Y2013]
ORDER BY [Royalities Y2013].F40 DESC;

Everytime I try to change the "5" after "SELECT TOP" to anything other than an integer I get an error saying that the command contains invalid arguments.

Seems like it should be easy, but I just can't get it to work Any help would be much appreciated!

Thanks!

[help_me_with_access]

Bril I don't think select statements in general can take arguments. from a programming perspective that doesn't make any bit of sense anyway.

but are you aware that in cases like this with office programs, any situation where concatenation is not possible through the interface, it can probably be a achieved with the accompanying code project?

although the method of creating object definitions in any office program with visual basic is completely undesirable, more or less because it's not common as a development practice, it is certainly an alternative in this situation.

try coding a new query definition in visual basic using the input box in a concatenation scenario. that should work fine. in access though, doing this multiple times requires deleting dataset objects repeatedly which makes good use of temporary data storage. as a result, database file bloat can be a problem, but you have to be repeating operations like this many many times over for it to become a relevant issue.

[Bril]

Thank you help_me_with_access. I wasn't aware that they couldn't take arguments (don't have much experience with SQL). It's not a function that would be used much, just a "fluff" addition my boss had wanted for his use, so I'll see if I bother tackling it in visual basic :P

Thanks again for the help!