Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Location
    Petersburg, Virginia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Order By on a Report using a Variable (XP)

    I have a tab form with command buttons that allow the user to sort by certain fields. E.g., the user may sort by client name. The command button runs this code on the On Click event:
    Me.RecordSource = "select * from [qry_recerts] where " & sVar & " ORDER BY [pat_lastname], [pat_firstname]"
    (sVar has already been established as a variable for filtering records, e.g., [area]="B")

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Order By on a Report using a Variable (XP)

    I believe you need to employ the orderby and orderbyon properties of the report to affect the sort order of a report.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Order By on a Report using a Variable (XP)

    Reports don't use the sort order of their recordsource, so I am not surprised your second attempt didn't work.

    But your first version looks OK.
    Where did you declare the variable sOrder? I it was decalred within the code for the form then the report does not know about it.

    Try msgbox(sOrder) before the me.orderby = sOrder.

    You could try this in the open event for the report
    me.orderby = forms!myform.orderby
    me.orderbyon = true

    Where myform is the form you open the report from.
    Regards
    John



  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Order By on a Report using a Variable (XP)

    On the report, go to sorting and grouping, add the field to sort on and indicate ascending or descending.

    Or you can add a field for sorting purposes to the report recordset to sort on and place it in the report. Make it invisible and sort on that using the sorting and grouping feature.

    HTH
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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