Sorting numbers on a report

[paul123]

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

[June7]

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

[ssanfu]

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.

[June7]

Hah! I got there first ssanfu! LOL

[ssanfu]

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:

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:

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

[ssanfu]

Hah! I got there first ssanfu! LOL

This time....

[June7]

Darn! You got the function in first!