Results 1 to 3 of 3
  1. #1
    Bril is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012

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

    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!


  2. #2
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    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.

  3. #3
    Bril is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    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!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 03-29-2012, 02:32 PM
  2. Replies: 8
    Last Post: 08-05-2011, 02:55 PM
  3. Replies: 16
    Last Post: 07-22-2011, 09:23 AM
  4. ComboBox "Select" and "DLookUp"
    By witooldas in forum Forms
    Replies: 0
    Last Post: 03-23-2011, 03:31 AM
  5. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums