Results 1 to 7 of 7
  1. #1
    accessnewbie352 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    46

    Sort DESC not working

    I am trying to create a macro to sort my values high to low.



    The expression I have is: ="[TableA].[FieldtoSort] DESC"
    SetValue: =[Forms]![TableA].[OrderByOn]
    Expression = Yes


    This appears to be working (i see the values are correctly sorted) but then the values do not look sorted anymore, then value re-starts sorting again.

    In the database for example, I would see the below after applying the macro:
    100
    99
    98
    97
    -1
    60
    59
    58
    41
    40

    Am I missing something in the expression?

  2. #2
    accessnewbie352 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    46
    Actually I think the way it was sorting was by text format. I updated the expression so now it is sorting correctly, with the exception of NEGATIVE numbers. For example it is sorting, 105,104,103,-102,101. How do I make the sort descend by value of the number (so negatives would be at the bottom).

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I don't use macros. Not familiar with what you are doing.

    Try using Val() function.
    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.

  4. #4
    accessnewbie352 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    46
    That did not work. I think it's really trying to make access recognize that -1 is < than 1. Is there a way to do this?

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Need more info on what you are doing so I can replicate the issue.
    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.

  6. #6
    accessnewbie352 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    46
    Create a table and plug in sample numbers: 2 fields.
    Create query to do a calculated expression that will generate negative and positive numbers.
    Format number so it is in percentage format.

    SELECT SORCHECKTBL.[Field1], SORCHECKTBL.[Field2], Format(([Field1]-[Field2]),"0.0%") AS SORTTHIS
    FROM SORCHECKTBL;

    When I try sorting this in descending form, negative number goes on top (access thinks it is a bigger value). How do I make it so it recognizes that negative is smaller than positive.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why bother with formatting in query? Format function results in a string value, no longer a number and therefore will not sort like number.

    The negatives go on top because alpha sort applies - although I thought DESC should put punctuation at end so this is still odd.

    Point is, don't apply Format function on field you want to sort as number. Create another field for the display with formatting or use textbox Format property on form/report.

    SELECT SORCHECKTBL.[Field1], SORCHECKTBL.[Field2], Format([Field1]-[Field2],"0.0%") AS NumPct FROM SORCHECKTBL ORDER BY [Field1]-[Field2] DESC;
    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.

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

Similar Threads

  1. ASC or DESC Sort Based On Another Field
    By KikoMatsing in forum Access
    Replies: 1
    Last Post: 08-12-2014, 10:34 AM
  2. Macro event depending sorting ASC or DESC
    By domivaxaccess in forum Forms
    Replies: 1
    Last Post: 04-13-2014, 09:50 AM
  3. Asc/Desc/reset sort button code
    By mightyteegar in forum Programming
    Replies: 2
    Last Post: 10-30-2012, 04:24 PM
  4. Sort function not working correctly
    By kristyspdx in forum Reports
    Replies: 2
    Last Post: 10-09-2012, 11:04 AM
  5. ORDER BY error when using DESC in an IIf
    By Smitoris in forum Queries
    Replies: 2
    Last Post: 10-30-2011, 02:48 AM

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