Results 1 to 6 of 6

Thread: Sorting Problem

  1. #1
    Join Date
    Jul 2005
    Posts
    8

    Unanswered: Sorting Problem

    I have the following data in a table machnumlkup
    with the field set as test

    When I sort I get
    1
    10
    11
    12
    2
    3
    4
    5
    6
    7
    8
    9
    All
    Auto Gage
    Old #5
    Sort
    Trough

    How do I set the sort so I get in the query and in the report?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    All
    Auto Gage
    Old #5
    Sort Trough

    I'd rather not add leading zeros to the single digit numbers?

    Thank you.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's kind of the problem. those aren't numbers, they're strings. There have been quite a few questions on this topic in this forum, I would try searching to see if you can find your answer.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    That's kind of the problem. those aren't numbers, they're strings. There have been quite a few questions on this topic in this forum, I would try searching to see if you can find your answer.
    And judging by the example Ted, he's not gonna find a solution with THAT mixed bag of numbers and alpha ... At least not without a BUNCH of mindbusting workarounds ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a possible mindbusting workaround:
    SELECT * FROM junk ORDER BY right$("00000" & alfa, 5);

    should do it for the 5-character text field "alfa" (adjust "00000" and 5 to suit field width)

    it's not very pretty tho!

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Mar 2006
    Location
    Charleston, SC
    Posts
    4

    temp table and regular expressions

    you could do something like this only if there was never 100 or over...

    I just realized this was for access. My post was for Server 2k, but you should be able to do something like this if it allows regular expressions

    insert into #temp --or use @temp
    (columnName, SortOrder)

    select columnName, 1 'sortOrder' from tableName
    where columnName like '[0-9]%'
    and columnName not like '[0-9][0-9]%'

    Union All

    select columnName, 2 'sortOrder' from tableName
    where columnName like '[0-9][0-9]%'

    Union All

    select columnName, 3 'sortOrder' from tableName
    where columnName like '[a-z,A-Z]%'

    select * from #temp
    order by 'sortOrder', columnName
    Last edited by BenRose3d; 03-05-06 at 01:57.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by sfoot0309
    ....
    I'd rather not add leading zeros to the single digit numbers?.....
    unless some of the above answers work for you the only other alternatives I can think of are:-
    to put a leading space(s) in front of the numbers,
    or
    to create a column called say sortseq and resolve your sort order on that.
    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
  •