Page 1 of 2 12 LastLast
Results 1 to 15 of 25

Thread: Select TOP

  1. #1
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Unanswered: Select TOP

    When using Select Top and a constant it functions good.
    i.e SELECT TOP 112 FROM yourTable;

    How can I get the value of that constant from a TextBox on a Form? i.e to be a variable

    I tried
    SELECT TOP [Forms]![yourFormName]![yourTextBoxName] FROM yourTable;

    but it says, a wrong SQL.....

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I bet that first one doesn't work!

    Nah - you can't do that. It is a statement, not an expression. You'll need to build something up dynamically (there are other ways but they are far worse than dynamic SQL).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Thanks for your reply,
    why should the first one not function? Just try it and see.

    Also you can use expressions in a SELECT statement, such as
    SELECT id,sName, field3, field9 FROM yourTable
    WHERE anyNumericFIELD > Forms!yourFormName!yourTxtBoxName




  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I meant the first statement has no columns specified!

    Yes, a SQL statement allows expressions in parts but the TOP n part is a statement, not an expression.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You could build the SQL statement in VBA and then execute it. Then you would achieve the result you're after.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Quote Originally Posted by StarTrekker
    You could build the SQL statement in VBA and then execute it. Then you would achieve the result you're after.
    Yes, I tried this
    SELECT TOP Forms!ourFormName!ourTextBox From ourTable

    but I get a syntax Error..
    Could you please make an example for what you mean..
    Thanks

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Still no column names...

    see post #4
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by hammbakka
    ...but I get a syntax Error..
    Could you please make an example for what you mean..
    you need to specify the name of the column that the SQL engine should be searching for. Taking a step back think of it as if someone asked you give me the top from atable
    the top what
    ..the top product ID'sm, the top prices, the top other ID. computers are dumb.. they need to be explcitily told what to look for

    say the name of the column you want the top n of is called MyColumnName

    then
    Code:
    SELECT TOP 112 MyColumnName From MyTable
    should do the trick
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Hello healdem,
    thanks for your answer,
    I know you should give a column name after the SELECT TOP
    [I might forgot it in my first thread] ,

    my question was,
    how could I make this 112 as a variable and not a constant.
    I'd like to read it from a text box on a form.

    Thanks

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You'd have to resort to dynamic SQL built up in VBA.
    George
    Home | Blog

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, as suggested in post #5
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Thanks a lot, can you give an example for the dynamic SQL..

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Dim SQL As String
    
    SQL = "SELECT TOP " & Forms![]... & " FROM mytable ORDER BY some_field"
    George
    Home | Blog

  14. #14
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Thanks, I'll try....

  15. #15
    Join Date
    Jul 2009
    Posts
    15
    Were you able to get it to work?? im trying the same thing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •