sort a column

G

GhysBoy

Hi all,

In a table have some value like

1-1-1
1-11-1
1-1-2
1-2-1
1-21-2


I want to order this like
1-1-1
1-1-2
1-2-1
1-3-1
1-4-1
1-5-1
1-6-1
1-7-1
1-8-1
1-9-1
1-10-1

Is it a text and when i class order alpha his like

1-1-1
1-10-1
1-2-1
....

Do you have way for me... I thinks in a query create column to use first 1
and value between - - ...


Thanks a lot for your help
 
S

Stefan Hoffmann

hi,
I want to order this like
1-1-1
1-1-2
Is it a text and when i class order alpha his like

1-1-1
1-10-1
1-2-1
...

Do you have way for me... I thinks in a query create column to use first 1
and value between - - ...
Click to expand...
You need a function like

Public Function MakeAlphaSort(AString As String) As String

Const PAD As String = "00000000"

Dim Count As Long
Dim Length As Long
Dim Result As String
Dim Strings() As String

Length = Len(PAD)
Strings = Split(AString, "-")

Result = ""
For Count = 0 To UBound(Strings())
Result = Result & Right(PAD & Strings(Count), Length)
Next Count

MakeAlphaSort = Result

End Function

to get a sortable column.


mfG
--> stefan <--
 
Top