3 Replies Latest reply on Oct 12, 2006 11:39 AM by twodaend

    Correct Sort Numbers Stored As Text

      I have some document numbers stored as text values that I would like to sort by in the correct order. However, what I'm getting is 1-101, 1-10A, 1-11, 1-14, 1-17A, 1-17A.i, 1-19, 1-2, 1-21, 1-30, 1.35, 1.47, 1-4-A, 1-5, 1-7, 1-A, 1-B, 1-C. These are the document's ID so that is why the inconsistancy. I wish they would have done better with this, but these ar old docs and and all I have to work with. This is just a small set. I have many more begining with 2's, 3's, and so on.

      I was wondering how can I get these to be sorted in the correct order from smallet to largest. I.E.
      1-2, 1-4-A, 1-5, 1-7, 1-10A, 1-11, 1-14, 1-17A, 1-17A.i, 1-19, 1-21, 1-30, 1.35, 1.47, 1-101,1-A, 1-B, 1-C

      The ABC can be at the beggining or end, does not matter just as long as the numbers are correct.

      Is this at all possible. Thanks for the help.
        • 1. Re: Correct Sort Numbers Stored As Text
          MikerRoo Level 1
          These are stored in a DB, right?

          It is very important to know which one (Access, MS SQL. etc.) because the needed syntax is DB specific.

          Also is "1-101' one id or a range?
          • 2. Re: Correct Sort Numbers Stored As Text
            My first concern would be the inconsistent way the values are stored:

            Depending on how you are using the data and how often they change, you might want to create a look-up table that holds the native ID (like above) and then a well formed id (or series of columns to make the id) that will search and sort correctly.
            • 3. Re: Correct Sort Numbers Stored As Text
              twodaend Level 1
              It is stored in a MS Access DB. I think I figured out how to get things in the correct order. Since there is an inconsistancy in the naming convention, I made a way to make it consistent. I have 2 columns, 1 with the document ID number and the other with just the number of the document (1-17A would have 17 for the second column.) This way I can sot first based on the second column since they are all numeric and the sort for the first column of I have duplicated in the second column.

              For example 1-17A and 1-17A.i would both have 17 in the second column, but by sorting by both columns, the order is correct and 1-17A comes before 1-17A.i. For dcoument ID's that don't have a number and rather a letter such ast 1-A, I assing a number of 1000 for A, 1001 for B and so on. This way they too have a number and will usually appear at the end of the list.