Results 1 to 6 of 6
  1. #1
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    98

    Using "Sort" and sorting by Numerical value rather than Alph value !

    I've created a a report which includes a "Rank" Field in each record. I want to list my records according to the "Rank" field in the order 1, 2, 3 ..... 1900 etc. I've spent hours on this and whilst I've found a solution... it isn't simple or elegant.... Just wondering if anyone else would have a better idea....



    The records are ranked by a number between 1 and 1900. When I introduce a "Sort" function, I only get options to sort A-Z and Z-A (with the criteria for the sort being the first characters in the string.)

    Even when I select "Sort by Entire Value" rather than the "First Character" or "First & Second Character" it is looking at alpha strings reading from left to right and not the entire Numeric string. Therefore... for example... 125 is listed before 35.... and 567 is listed before 98.

    In order to make this sort numerically, it seems that I have to add leading Zeros to my rank numbers (e.g. 0001, 0034, 0125) so that the sort fuction reads the "entire value" This means that I've got to go through 999 records and put leading zeros ahead of the number....

    Is there a better way to do this?

    Long suffering Jimbo !

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Would the query for the report not include data sorted on your rank field?

    It sound like your rank field may be a text field too


    Sent from my iPhone using Tapatalk

  3. #3
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    98
    Andy49,

    Agggggh !! Of course.... The Rank field is indeed a Text field in the table.... (Stupid Stupid Stupid!!!)

    and I don't understand you first sentence.. can you re-phrase it.

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Presumably your report has a query where it gets its data from? Perhaps you should focus on "ordering" the query


    Sent from my iPhone using Tapatalk

  5. #5
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    98
    Hi Andy49,

    Many Thanks... I feel such a tit asking the questions now. I've changed the field to numeric and of course it sorts properly in the report now.

    The Query.... Yes, that seemed fine to me. That's where I was trying to make my sort changes, but it simply didn't work.... and I know why now! All I needed was the prompt.

    Thanks for your time.

    Embarrassed Jimbo

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    No probs. It's always the silly stuff that we make mistakes with. Best of luck on your project.


    Sent from my iPhone using Tapatalk

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

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  2. Replies: 1
    Last Post: 02-19-2015, 03:29 AM
  3. Replies: 1
    Last Post: 12-28-2012, 02:54 PM
  4. Replies: 2
    Last Post: 06-07-2012, 02:59 PM
  5. Replies: 3
    Last Post: 11-15-2011, 02:41 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