Results 1 to 9 of 9

Thread: SQL row count

  1. #1
    Join Date
    Jun 2002
    Posts
    72

    Unanswered: SQL row count

    I need to restrict the number of rows output using SQL, is there a way to do this, for example:

    SELECT WORKER.WORKER_NAME FROM WORKERS WHERE ROWCOUNT < 6 ORDER BY WORKER.WORKER_NAME

    should bring back the first 5 records in Worker order.

  2. #2
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225

    Re: SQL row count

    Try this:


    SELECT TOP 5 WORKER.WORKER_NAME FROM WORKERS WHERE ROWCOUNT < 6 ORDER BY WORKER.WORKER_NAME

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, except the rowcount bit should be removed

    SELECT TOP 5
    WORKER_NAME
    FROM WORKERS
    ORDER BY WORKER_NAME


    rudy
    http://r937.com/

  4. #4
    Join Date
    Jun 2002
    Posts
    72
    Thanks, that worked fine but how can I retrieve the value from a field in a form. eg

    SELECT TOP forms!worker!recout
    WORKER_NAME
    FROM WORKERS
    ORDER BY WORKER_NAME

    doesn't work. Can it be done this way?

  5. #5
    Join Date
    May 2003
    Location
    Australia
    Posts
    7
    SELECT TOP " & IIf(IsNumeric(forms!worker!recout),forms!worker!re cout,0) & "
    WORKER_NAME
    FROM WORKERS
    ORDER BY WORKER_NAME

  6. #6
    Join Date
    Jun 2002
    Posts
    72
    No that doesn't work, I keep getting the error message ....

    The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorect.

  7. #7
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    SELECT TOP forms!worker!recout
    I don't think the number of records to be returned can be a parameter - i.e. derived from a control on a form.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  8. #8
    Join Date
    May 2003
    Location
    Australia
    Posts
    7
    Should work in code but it won't work in a query.

  9. #9
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225
    If you just want to display the value of the control in the query you can just add it as one of the colums.

Posting Permissions

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