Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    Thumbs up Unanswered: Sort A Documents by there number 1.1 .1.3 1.1.2

    This been Bugging me for a Long Long time

    Wanted a way to sort Document

    by there dot number

    1.0 , 1.1 , 1.2 , 1.3.1 , 1.3.2

    Code:
    Function SOPnumberOrder(SOP, index)
        tmp = Split(SOP, ".", -1, vbTextCompare)
        If index > UBound(tmp) Then
            index = 0        'UBound(tmp)
        End If
        SOPnumberOrder = tmp(index)
    End Function
    now in the QUERY

    SELECT SOP.SOPID, SOP.SOPNumber, Val(SOPnumberOrder([sopnumber],0)) AS col1, Val(SOPnumberOrder([sopnumber],1)) AS col2, Val(SOPnumberOrder([sopnumber],2)) AS col3
    FROM SOP
    ORDER BY Val(SOPnumberOrder([sopnumber],0)), Val(SOPnumberOrder([sopnumber],1)), Val(SOPnumberOrder([sopnumber],2));

    I Name it "SOP SORT" now When i print then they are in . order
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  2. #2
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    Kyle,

    Use a function, but return the number like:

    Nnn.nnn.nnn.nnn.nnn.

    Then just sort normally.

    Hth
    Wayne

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think Myle was informing us of his cunning solution to the problem, not requesting solutions

    FWIW when I've had these sort of issues in the past I've padded the numbers with spaces on storing and then stripped out any spaces on retrieving

    so
    Code:
    1.3.15.1
    is stored as
      1.  3. 15.  1
    .. it does mean you have to make assumptions about how large the maximum value any component can be. ferinstance in the above example I guessed 3 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
  •