Results 1 to 5 of 5
  1. #1
    seattleite is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    2

    Report column does not OrderBy DESC, only ASC

    I've added sorting on my Report columns on click, alternating between ascending and descending. I'm using the same logic for 10+ columns and they work great except for one, which will not order by descending. The code I am using:

    Private Sub Item_Description_Label_Click()
    If Me.OrderBy = "[Item Description]" Then
    Me.OrderBy = "[Item Description] DESC"
    Else
    Me.OrderBy = "[Item Description]"
    End If
    Me.OrderByOn = True
    End Sub



    When I debug, I can see that on the second click, since Me.OrderBy = "[Item Description]", the If statement evaluates to True and Me.OrderBy = "[Item Description] DESC" is reached. However, I see no difference in the actual Report. I'm stumped as I have another column with the same data type (long text) where both order bys work fine. Help would be much appreciated!
    Attached Files Attached Files
    Last edited by seattleite; 04-16-2021 at 02:07 PM. Reason: Adding attachment

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to provide db for analysis, follow instructions at bottom of my post.

    AFAIK, sorting on memo/long text field only uses first 255 characters.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    seattleite is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    2
    Thanks, attached the db.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sorting on long text (memo) is not possible via the ribbon or right click tools. Although, can apply a sort with that field in a query (only first 255 characters will be considered). However, not having any better luck getting VBA to apply DESC sort to report OrderBy property with this field. Not something I've ever tried before.

    I recommend changing field to short text. 255 characters should be more than enough for an item description. So far, the longest description is 67 characters. I avoid memo/long text fields as much as possible.

    Also advise not to use spaces nor punctuation/special characters in naming convention.
    Last edited by June7; 04-17-2021 at 11:34 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    It is certainly true that the sort functionality is not available for memo fields in reports.
    However, it is possible to sort a memo field in descending order in a report but only with certain provisos:
    1. The data isn't based on an SQL Server table. Local or linked Access tables are both fine
    2. The report data is based on a query and the memo field sort is done there.

    For example, I just created this quick example report based on linked Access table data from a fictitious school
    Click image for larger version. 

Name:	Capture.PNG 
Views:	12 
Size:	78.2 KB 
ID:	45011
    Last edited by isladogs; 04-17-2021 at 11:16 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Order By DateField... ASC if this / DESC if that
    By MatthewGrace in forum Queries
    Replies: 7
    Last Post: 12-15-2015, 03:30 PM
  2. Sort DESC not working
    By accessnewbie352 in forum Macros
    Replies: 6
    Last Post: 01-15-2015, 02:27 PM
  3. How Can I: OrderBy inside an Other OrderBy
    By RichardAnderson in forum Forms
    Replies: 1
    Last Post: 08-23-2013, 01:04 PM
  4. ORDER BY error when using DESC in an IIf
    By Smitoris in forum Queries
    Replies: 2
    Last Post: 10-30-2011, 02:48 AM
  5. prompt for orderby in report
    By nkuebelbeck in forum Access
    Replies: 2
    Last Post: 08-18-2011, 12:57 PM

Tags for this Thread

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