Results 1 to 7 of 7
  1. #1
    paul123 is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    1

    Sorting numbers on a report

    Hi,
    I have a table that contains a text field [drawingnumber] and a report that displays the drawing numbers and I can not get the sorting correct.

    Each record has a drawing number like:
    379-02-0002
    379-02-0002-3
    379-02-0002-5
    379-02-0002-9
    379-02-0002-11



    The report sorts them like this:
    379-02-0002-11
    379-02-0002
    379-02-0002-3
    379-02-0002-5
    379-02-0002-9

    What I want is this:
    379-02-0002
    379-02-0002-3
    379-02-0002-5
    379-02-0002-9
    379-02-0002-11

    Is there a way to get the sort order the way I want it?

    Thanks
    Paul

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,616
    These are not numbers, they are text. That means alphabetic sort and each character is evaluated in the sort operation. Everything is fine until after the last hyphen. 11 shows before 9 because 1 is the character compared to 9 to determine order. Not quite sure why 11 comes before the blank. If you want these values to sort by the apparent numerical order, need placeholder zeros. In other words, the text strings must all be the same length.

    379-02-0002-00
    379-02-0002-03
    379-02-0002-05
    379-02-0002-09
    379-02-0002-11
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by paul123 View Post
    Hi,
    I have a table that contains a text field [drawingnumber] and a report that displays the drawing numbers and I can not get the sorting correct.

    Each record has a drawing number like:
    379-02-0002
    379-02-0002-3
    379-02-0002-5
    379-02-0002-9
    379-02-0002-11

    The report sorts them like this:
    379-02-0002-11
    379-02-0002
    379-02-0002-3
    379-02-0002-5
    379-02-0002-9

    What I want is this:
    379-02-0002
    379-02-0002-3
    379-02-0002-5
    379-02-0002-9
    379-02-0002-11

    Is there a way to get the sort order the way I want it?

    Thanks
    Paul
    Even though they are numbers to you, because the field is a text type field, the characters are sorted left to right.

    When I do the sort, I get:

    Drawing
    379-02-0002
    379-02-0002-11
    379-02-0002-3
    379-02-0002-5
    379-02-0002-9

    When you go left to right, by character, "1" (not 11) sorts before "3". The quote marks indicate that these are "text", not numbers.

    You would have to enter the drawing "numbers" like this:

    Drawing
    379-02-0002-00
    379-02-0002-03
    379-02-0002-05
    379-02-0002-09
    379-02-0002-11

    to get them sorted the way you want. Or add a sort order field.

    Presumably, each grouping of numbers has a meaning. this would violate normalization rules. i.e. the data is not atomic.

    Maybe you write a UDF to return a formatted drawing number in the query, then sort on that column.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,616
    Hah! I got there first ssanfu! LOL
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This seemed to work for me. Provided the drawing number format will be

    "xxx-xx-xxxx" or "xxx-xx-xxxx-x" or "xxx-xx-xxxx-xx"

    try this:

    add this function:
    Code:
    Public Function DwgFormat(pDwg As String) As String
       Dim LenDwg As Integer
       LenDwg = 14 - Len(Trim(pDwg))
    
       Select Case LenDwg
          Case 0
             DwgFormat = pDwg
          Case 1
             DwgFormat = Left(pDwg, 12) & "0" & Right(pDwg, 1)
          Case 3
             DwgFormat = pDwg & "-00"
       End Select
    
    End Function
    This is the query I usedto test:

    Code:
    SELECT Table1.Drawing, DwgFormat([Drawing]) AS Expr1
    FROM Table1
    ORDER BY DwgFormat([Drawing]);

  6. #6
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by June7 View Post
    Hah! I got there first ssanfu! LOL

    This time....

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,616
    Darn! You got the function in first!
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Report Sorting Issue
    By cbgroves in forum Reports
    Replies: 14
    Last Post: 12-01-2011, 08:44 AM
  2. Sorting and Populating Report Headings
    By bpowers2010 in forum Reports
    Replies: 1
    Last Post: 08-11-2010, 05:05 PM
  3. Incorrect sorting in report.
    By jonesy29847 in forum Reports
    Replies: 2
    Last Post: 06-16-2010, 05:56 PM
  4. sorting a field in report
    By Philangr8 in forum Reports
    Replies: 3
    Last Post: 08-26-2009, 05:38 PM
  5. Sorting data in a report
    By rjsiler in forum Reports
    Replies: 1
    Last Post: 08-04-2008, 01:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums