Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178

    Question Unanswered: order by textfield containing integer values

    i have a field that stores locations such as 100-1-1.
    if it goes from 100-1-1 to 100-12-2

    how can I order it so that it doesn't appear like:
    100-10-1
    100-10-2
    100-1-1
    100-11-1
    100-11-2
    100-1-2
    100-12-1
    100-12-2
    100-2-1
    100-2-2
    100-3-1
    100-3-2
    etc.

    but rather appears like:
    100-1-1
    100-1-2
    100-2-1
    100-2-2
    100-3-1
    100-3-2
    ...
    100-11-2
    100-12-1
    100-12-2

    any suggestions
    thanks in advance
    ActionAnt

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Can you associated (or assign) an ordering number to each location?

  3. #3
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    maybe, but thats going to be a big job.
    any other suggestions

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    The reasoning for my suggestion was that it appears the your data orders like a string ... The only other realistic way would be to split each component part and order it that way ...

  5. #5
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    thats better thanks

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Meh, it can be done

    You'll have to do a little bit of parsing.

    ORDER BY CInt(REPLACE(keyField, "-", ""))

    You may have to put that in your select clause too. Some engines don't like you putting things in the ORDER BY clause if they don't also appear in the SELECT cluase.

  7. #7
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    looks good but won't this still happen?
    100-11-1
    100-11-2
    100-1-2

  8. #8
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    SQL doesn't recognise "replace" either

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ah yes, so it would.

    you ARE going to have to parse the whole thing out afterall....

    I'm a bit slammed atm, but I'd be willing to help you parse that out, either to return the data or FOR THE LOVE OF CHRIST restructure it.


  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by actionant
    SQL doesn't recognise "replace" either
    Are you hitting some odd external db?

    Replace is a valid access AND sql server function. You can check it's usage by typing "REPLACE function" in the help system.

  11. #11
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    happens to the best of us
    don't worry, was busy doing that anyway

  12. #12
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    says its an unrecognised function

Posting Permissions

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