Results 1 to 6 of 6
  1. #1
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283

    Sorting a Report field based on its value

    I am using access 2010 and have a report that is driven off of a query. What I am trying to do is if the Field [BW] is >0 then sort Descending and if it is < 0 (meaning a negative number) then sort Ascending.



    Basically if the number is greater than 0 sort like this example 6, 5, 4, 3, 2, 1
    and if the number is less than 0 sort like this example -6, -5, -4, -3, -2, -1

    This is driving me crazy

    Any help would be appreciated

    Bret

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    My first thought is adding a sort field to the query, and have that be the primary sort on the report. For instance, 1 for >=0 and 2 for <0. Then have the secondary sort on the report be on the absolute value of the amount field (you can add that to the query too if desired).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    I can;t really mess with the queries as there is a lot more parts using them.

    Coudl this be done with an If the else somehow

    If value >0 then DESC else ASC

    That is the road I was going down but could not get anything I tried to work

  4. #4
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    I figured it out!!!

    Private Sub Report_GotFocus()
    If [BW] > 0 Then
    DoCmd.SetOrderBy "BW DESC"
    Else
    DoCmd.SetOrderBy "BW ASC"
    End If
    End Sub

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You might be able to use an expression in the report's sorting rather than a field (though adding a field to the source query shouldn't affect other users).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't think that will work if the source query returns both positive and negative numbers.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 2
    Last Post: 03-07-2013, 04:50 PM
  2. Sorting Report Based off of a Combo Box in a Form
    By datadude60 in forum Reports
    Replies: 4
    Last Post: 04-30-2012, 10:38 AM
  3. Report based on particular field
    By Sorbz62 in forum Reports
    Replies: 24
    Last Post: 02-25-2012, 04:40 PM
  4. Sorting a Report by a Calculated field
    By mulefeathers in forum Reports
    Replies: 1
    Last Post: 05-22-2010, 08:21 PM
  5. sorting a field in report
    By Philangr8 in forum Reports
    Replies: 3
    Last Post: 08-26-2009, 05:38 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