Without using a leading zero, how do you sort 1-9 before 10-20? .

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I work with federal documents that will not allow me to change the numbering
of items. I need to sort in ascending order, yet I cannot seem to get '2' to
sort before '10'. Any ideas??

I am working with numbers such as 1.0 through 1.23.15
 
Don said:
I work with federal documents that will not allow me to change the numbering
of items. I need to sort in ascending order, yet I cannot seem to get '2' to
sort before '10'. Any ideas??

I am working with numbers such as 1.0 through 1.23.15
Click to expand...

Those are not numbers. They could be described as Codes perhaps, but they are
text strings that happen to predominantly contain digits rather than letters and
will therefore sort as text.

It's a common misapplication of the label. People always refer to Social
Security "numbers" and license plate "numbers" (serial "numbers", part
"numbers"), etc., but if they have dashes or more than one decimal point then
that are not numbers in the literal sense which is what the database cares
about.

You could sort on a derived value of Val([FieldName]) which would give you a
numerical return value made up of all of the characters up until the first
character that cannot be interpreted properly as a number ("1.23.15" will return
1.23). That might be closer to what you want. To get any better you would need
to derive multiple columns consisting of each numerical "piece" and sort on all
of those.
 
Hi,
You could add an Order By clause using a calculated field.
Something like:

ORDER BY CCur(yourField)

I chose Currency for the datatype.
1.23.15 is not a number as far as I know, so I don't know how you will deal with that
 
The approach I would use is to handle each of the
component numbers between periods as individual numbers
and inserting the required number of leading zeros then
all strung together. My example allows for each set to be
4 digits in length (0 to 9999). Place this genearted
number in a listbox with sorted = true for a more
extensive test. My sample code simply puts it a the
caption of the form.

Copy / Paste this to a new project:

Private Sub Form_Load()
Dim Txt As String
Dim Pos(1) As Integer

Txt = "1.23.45"
Pos(0) = InStr(1, Txt, ".")
Pos(1) = InStr(Pos(0) + 1, Txt, ".")
Form1.Caption = Format(Mid(Txt, 1, Pos(0) - 1), "0000")
& _
Format(Mid(Txt, Pos(0) + 1, Pos(1) - 1), "0000") & _
Format(Mid(Txt, Pos(1) + 1), "0000")

End Sub
 
The trick is to breakup each part of the document ID and convert it to
a true number. Then sort on each part.
Put this function into a module.

Function SortSet(DocID As String, Pos As Integer)
Dim SubID() As String
SortSet = 0
On Error GoTo End_SortSet
SubID = Split(DocID, ".")
SortSet = CInt(SubID(Pos - 1))
End_Sortset:
End Function

Your query would look something like this:
SELECT Docs.*
FROM Docs
ORDER BY SortSet([DocID],1), SortSet([DocID],2), SortSet([DocID],3),
SortSet([DocID],4);

P.S. This function will return zero if there's a missing part of the
Doc ID. (i.e. SortSet("1.25",3) will return zero.)

Tom Collins



| I work with federal documents that will not allow me to change the
numbering
| of items. I need to sort in ascending order, yet I cannot seem to
get '2' to
| sort before '10'. Any ideas??
|
| I am working with numbers such as 1.0 through 1.23.15
 
Back
Top