Sorting numbers like 1.10.5 and 1.10.5.1 in text field

C

CCross

I have imported from excel a list of numbers such as 1.10.5, 1.10.5.1,
1.10.5.10, 1.10.5.1.a and would like to know if there is a way to sort these
numbers so I get the following:
1.10.5
1.10.5.1
1.10.5.1.a
1.10.5.10
Any help greatly appreciated.
Thanks,
CCross
 
A

Allen Browne

Here's another way to solve this, if we can assume that each point value is
a number between 0 and 999, and this is Access 2000 or later.

Copy the function below into a module. Then in your query:
ORDER BY PointSort([Field1])
replacing Field1 with the name of your field

The function returns a Variant of subtype String, with 3 digits for each
point number, to any depth. Sorting by this string should give the desired
results.

Public Function PointSort(varInput As Variant, _
Optional strDelim As String = ".") As Variant
'Purpose: Sort on a field such as "5.11.2"
Dim strOut As String
Dim i As Integer
Dim varArray As Variant

PointSort = Null 'Initialize to null

If Not (IsError(varInput) Or strDelim = vbNullString) Then
If Not IsNull(varInput) Then
varArray = Split(varInput, strDelim)
For i = LBound(varArray) To UBound(varArray)
strOut = strOut & Format(varArray(i), "000")
Next
If strOut <> vbNullString Then
PointSort = strOut
End If
End If
End If
End Function
 
C

CCross

Allen, Thank you so much. I don't completely understand how it works, but it
does and right now that is all that matters. Thank you to everyone who
helped, I will definetely keep all information for future reference.
CCross

Allen Browne said:
Here's another way to solve this, if we can assume that each point value is
a number between 0 and 999, and this is Access 2000 or later.

Copy the function below into a module. Then in your query:
ORDER BY PointSort([Field1])
replacing Field1 with the name of your field

The function returns a Variant of subtype String, with 3 digits for each
point number, to any depth. Sorting by this string should give the desired
results.

Public Function PointSort(varInput As Variant, _
Optional strDelim As String = ".") As Variant
'Purpose: Sort on a field such as "5.11.2"
Dim strOut As String
Dim i As Integer
Dim varArray As Variant

PointSort = Null 'Initialize to null

If Not (IsError(varInput) Or strDelim = vbNullString) Then
If Not IsNull(varInput) Then
varArray = Split(varInput, strDelim)
For i = LBound(varArray) To UBound(varArray)
strOut = strOut & Format(varArray(i), "000")
Next
If strOut <> vbNullString Then
PointSort = strOut
End If
End If
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

DBS said:
Well, those aren't actually numbers.

What you'll have to do is split (or parse) those text strings using the
periods as delimiters, and then sort on the resulting values.

So for instance, "1.10.5.1.a" is parsed into:
Field1 = 1
Field2 = 10
Field3 = 5
Field4 = 1
Field5 = "a"

Make sure that numeric fields have a numeric data type, and you should
then
be able to sort by those fields, ie:
"ORDER BY Field1, Field2, Field3, Field4, Field5;"

HTH,

David
Click to expand...
Click to expand...
 
Top