Results 1 to 4 of 4

Thread: Top 5 - Help!

  1. #1
    Join Date
    Jan 2003
    Posts
    56

    Unhappy Unanswered: Top 5 - Help!

    Hi,

    I am trying to create a report which is filtered via a form. The form allows me to specify a product id from a combo box and when I click OK it show all of the associated orders in my report. This part is working fine but I would like to be able to only show the top five orders for each product using the quantity field as the criteria.

    I know you can use Top 5 in a query but can anyone tell me how to do the same when the filter is being applied via a form first?!

    Any suggestions would be great!

    Thanks,

    C.

  2. #2
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: Top 5 - Help!

    Find the query on which your report is based (see the Recordsource property of the report). Open the query-builder in SQL-mode and add
    " TOP 5 " after the first word "SELECT". That should do it, even when applying a filter to the report.

  3. #3
    Join Date
    Dec 2002
    Posts
    60

    Re: Top 5 - Help!

    You could try something like (put in report open event)

    Me.RecordSource = "SELECT TOP 5 * FROM tbltest WHERE [ProductID] = " & forms![form name]![prodID field] & " ORDER BY ..."

    This assumes product ID is a number. If it is text:

    Me.RecordSource = "SELECT TOP 5 * FROM tbltest WHERE [ProductID] = '" & forms![form name]![prodID field] & "' ORDER BY ..."

    (Added single quotes)

    The form "form name" will need to be open when this codes runs.

  4. #4
    Join Date
    Jan 2003
    Posts
    56

    Re: Top 5 - Help!

    Originally posted by bc301
    You could try something like (put in report open event)

    Me.RecordSource = "SELECT TOP 5 * FROM tbltest WHERE [ProductID] = " & forms![form name]![prodID field] & " ORDER BY ..."

    This assumes product ID is a number. If it is text:

    Me.RecordSource = "SELECT TOP 5 * FROM tbltest WHERE [ProductID] = '" & forms![form name]![prodID field] & "' ORDER BY ..."

    (Added single quotes)

    The form "form name" will need to be open when this codes runs.

    Cheers, that worked great. Thanks a million!!!
    C.

Posting Permissions

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