Results 1 to 2 of 2
  1. #1
    rer001 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Posts
    1

    Sorting text field containing both text and numbers sequencially

    My apologies if this has been answered already. We have a database that we use to track the tablets in our organization, a combination of Apple, Android, and Windows.




    We label them with a hostname depending on their branding. Example below.:


    APPLE01
    APPLE02
    APPLE03


    ANDROID01
    ANDROID02
    ANDROID03


    WINDOWS01
    WINDOWS02


    In the database we use the field name HOSTNAME for these entries. When sorting the field, due to it being a text field, we get the traditional sort of


    APPLE01
    APPLE02
    APPLE10
    APPLE100
    APPLE101
    APPLE11
    APPLE110


    ANDROID01
    ANDROID02
    ANDROID10
    ANDROID100
    ANDROID101
    ANDROID11
    ANDROID110


    We would like to have them show up as


    APPLE01
    APPLE02
    APPLE10
    APPLE11
    APPLE100
    APPLE101
    APPLE110
    ANDROID01
    ANDROID02
    ANDROID10
    ANDROID11
    ANDROID100
    ANDROID101
    ANDROID110


    What is the best way to tackle this? Thank you.

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Two options:

    1. Change your naming convention, so that they all have 3 digit numbers on the end.
    So APPLE01 would be APPLE001
    and APPLE10 would be APPLE010

    2. Split your entry into two calculated fields, one with the text, and one with the number, and sort by each of those.
    Here is an old link that shows a User Defined Function that can be used to pull a number out of a string: https://www.accessforums.net/program...field-659.html

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

Similar Threads

  1. Replies: 2
    Last Post: 06-02-2013, 08:07 PM
  2. Replies: 6
    Last Post: 07-03-2012, 12:27 PM
  3. Replies: 2
    Last Post: 05-05-2010, 02:52 PM
  4. Setting a field to only accept text characters, not numbers
    By USAFA2012 in forum Database Design
    Replies: 2
    Last Post: 03-09-2010, 12:37 PM
  5. Extract numbers from text string strored in a field.
    By khabdullah in forum Programming
    Replies: 2
    Last Post: 12-23-2007, 06:55 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