Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009

    Unanswered: How to correctly sort text and number values?

    I am trying to sort a field formatted as text with data similar to this:

    A1, A2, A3, A4, B1, B2, B3, B4

    I need to be able to sort this alpa-numerically. What happens right now is the data sorts like this:

    A1, A10, A11, A2, A20...

    How can I get a field like this to sort correctly?

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    If the data strictly have that format (a letter followed by a number) you can use a query like this:
    SELECT Some_Table.Original_Data
    FROM Some_Table
    ORDER BY Left([Original_Data],1), Val(Mid([Original_Data],2));
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    or consider padding the numbers with either spaces or zeros

    eg A001, A002, A003 and so on. proividing you make certain your padding is at least the same size as the exepcted numbers
    ie if you expect say upto 99, then consider using 3, possibly 2 digits
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts