Results 1 to 5 of 5
  1. #1
    geocan2006 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Posts
    34

    Proper Sorting query

    Hi,
    I have to sort a list in proper manner but I couldn't found the way, the required sorting as below
    Thanks
    Default Sorting Proper Sorting
    AB1000 AB11
    AB102 AB102
    AB11 AB1000


  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The field you are trying to sort is a text string and it is correctly sorting it 'alphabetically'
    If all records start with AB or with two letters then you can add a query field Mid(YourFieldName, 3) to grab the number part and sort that in ascending order.
    Place that field first in your query and untick the Show checkbox
    Last edited by isladogs; 05-16-2018 at 01:34 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

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    How to sort was answered, but an additional advice to avoid such problems in future.

    When some field values are constructed from strings and numbers, or from numbers, but you need the string be in text format, then make all part of result string of fixed length, an preceed/end them with special characters. For numeric parts - use preceeding "0"'s. I.e. in your example field values would be 'AB1000', 'AB0102', 'AB0011', or 'AB001000', 'AB000102', 'AB000011', depending on max possible number in numeric part. In case the string part length can vary too, the values may be like 'AB1000', 'A–0102', 'B–0011' (where I used CHAR(150) to extend string part).

    Edit. I assumed ASCII value of character determines the sort order, but it looks like regional settings would kick in - I couldn't find a passing non-litera character except a space to add at end of string part and to get proper sort order. Whatever I used, it did work only when used to precede the string. So only 'AB1000', 'A 0102', 'B 0011' will work

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Arvil's advice is sound.
    An alternative is to split the field into two: text part and number part and then create a composite index of both fields with unique values
    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

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Code:
    SELECT Left([MyValues],2) & Mid([MyValues],3) AS Expr1
    FROM tblMyTable ORDER BY CInt(Mid([MyValues],3));
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. How to make a proper crosstab query?
    By Michael.S90 in forum Queries
    Replies: 5
    Last Post: 05-24-2017, 04:38 PM
  2. Proper Grouping Of A Totals Query
    By Tambe257 in forum Queries
    Replies: 1
    Last Post: 03-24-2017, 04:29 PM
  3. Proper Syntax for IIf function in Query Criteria
    By Ecologist_Guy in forum Queries
    Replies: 4
    Last Post: 03-13-2017, 11:19 AM
  4. Replies: 2
    Last Post: 08-03-2014, 11:00 AM
  5. Replies: 6
    Last Post: 01-19-2014, 09:30 AM

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