Results 1 to 5 of 5
  1. #1
    TOPSie is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    101

    ORDER BY sort order?

    I have a fairly simple table and all the fields are SHORT TEXT. Most field contain just numbers.



    I have a query to select some records and the query has ORDER BY "Field5" (say).

    If all the selected records have the same number of digits in Field5 then the order is correct. But if records have Field5 with different numbers of digits (e.g. a set of records has 1,2,3,....,10,11,....20,.....) then the Query returns
    1 then 10, 11 etc then 2, then 21 - 29 then 3 then 30 etc.

    Is there anyway to force the ORDER BY to the "correct" order??

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Can only sort a text field with numbers if you add a sort field where you will have to manually decide on the sort order values. If you can, change the text to number type. When it's text, 111 comes before 22 because it's sorted like text just like aax comes before abc.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    When you are using alphanumeric field (i.e. text fields with numeric values in it), then you have use them as fixed length strings. So, when you field is e.g. 4 characters wide, you use values like "0001", "0111", or "9999". Then you can sort records properly. You can write an OnUpdate event for data entry form field, which takes care of it, replacing any changed value with expression like Right("0000" & YourField,4).

    Otherwise simply use numeric/integer field instead.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    If most fields contain numbers, why not change the field type to a numeric one?

    Or do you mean most records contain numbers for a given field? In which case you can’t change the field type

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    You can add a calculated field to the query that converts the text to numbers (CInt or CDouble), use this field as a sort field. Of course this only works if the field in all records only contain text values that can be converted to a number.

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

Similar Threads

  1. Replies: 5
    Last Post: 08-27-2019, 08:41 AM
  2. Sort Order problem
    By Chris@harveynorman in forum Reports
    Replies: 8
    Last Post: 11-22-2016, 10:42 PM
  3. Replies: 2
    Last Post: 03-07-2016, 05:02 PM
  4. Sort by Month Order
    By sk88 in forum Reports
    Replies: 1
    Last Post: 03-17-2015, 05:22 PM
  5. Sort order in a sub form
    By roar58 in forum Forms
    Replies: 1
    Last Post: 03-17-2012, 08:57 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