sorting text field function

[Ryan D]

i found the following function for MS Access to sort text fields with
numbers. this code is excately what i need, however i need the function to
use two "strDelimit" variables, spitting the string based on two different
characters, a "-" and a ".", can anyone help me? thank you.



Public Function fSortSpecial(strIn, _
Optional strDelimit As String = ".", _
Optional LnumSize As Long = 3) As String

'strDelimit = character(s) used to split string
'lNumsize = minimum number of characters to use for number string
'This function is designed to handle only integer number strings
Dim vSplit As Variant
Dim strReturn As String
Dim i As Long
Dim sFormat as string

sFormat = String(LnumSize,"0")
If Len(strIn & "") = 0 Then
fSortSpecial = ""
Else
vSplit = Split(strIn, strDelimit)
For i = LBound(vSplit) To UBound(vSplit)
If IsNumeric(vSplit(i)) Then
strReturn = strReturn & _
Format(Val(vSplit(i)),sFormat) & strDelimit
Else
strReturn = strReturn & vSplit(i) & strDelimit
End If
Next i

fSortSpecial = strReturn

End If
End Function

 

[Jeff Boyce]

Ryan

You've described "how".

"What"? Can you provide an example of the data you have and the way you
want it sorted?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ryan D]

the purpose is to sort a text field that has numeric characters, examples of
the data are;

AHU-R.1 C
B-1.1
CP-1
CP-2
CP-3
EUH1.8
TEF-R.3

i can get the function to work if the data has only a "-" or a ".", but i
need it to work if the data has both.

one suggestion i got was ;

"u need to first split a string on one char "-" after that u getting one
array and than traverse whole array in a loop an split every element in that
array depends upon another char ".""

but i dont know how to do that.

 

[Jeff Boyce]

OK, that's the first half... You provided an example of data.

Now please provide an example of how that data would look AFTER sorting it
the way you want it sorted.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Snell \(MVP\)]

Here's a function that I've used for such "mixed-format" strings -- what I
do is add a calculated field to the query that calls this function for the
text field, and then sort on the calculated field:

SELECT *
FROM YourTableName
ORDER BY ReturnSortValueForAlphaNumerics([NameOfTheTextField) ASC;



Public Function ReturnSortValueForAlphaNumerics(ByVal strOriginal As String)
As String
' ** LOGIC IS TO REPLACE EACH CHARACTER IN THE ORIGINAL STRING WITH A MULTI-
' ** CHARACTER "NUMBER" STRING THAT WILL SORT THE ORIGINAL STRING CORRECTLY.
Dim lngLoc As Long
Dim strSort As String, strT As String, strLoc As String

Const strDash As String = "-"
Const strNum As String = "[0-9]"


lngLoc = 1
strT = Left(strOriginal, 1)
strSort = Format(Abs(Not strT Like strNum) & IIf(IsNumeric(strT), "00",
Asc(strT)), "000")
strT = ""

Do
strLoc = Mid(strOriginal, lngLoc, 1)
If strLoc Like strNum Then
Do
strT = strT & strLoc
lngLoc = lngLoc + 1
strLoc = Mid(strOriginal, lngLoc, 1)
Loop While strLoc Like strNum
strSort = strSort & Right("!!!!!!!!!!" & CStr(Val(strT)), 10)
strT = ""

Else
If strLoc = strDash Then
strSort = strSort & "AAA"
Else
strSort = strSort & strLoc & "ZZ"
End If
lngLoc = lngLoc + 1
End If

Loop Until lngLoc > Len(strOriginal)

ReturnSortValueForAlphaNumerics = strSort

End Function

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Ryan D]

jeff, thanks for your help, here is an expanded list of data and how it
should be sorted. the function examines the data and adds zeros to numeric
characters

so "BS-1-2" translates to "BS-001-002-" and "EUH1.8" translates to "EUH1.008."

the problem is when i have data like "HWP-1.1", i would need this translated
to "HWP-001.001"

data should be sorted like the following;

AEF-R.1
AHU-R.1
AHU-R.1 C
AHU-R.2
AHU-R.2 C
B-1
B-1.1
B-1.2
B-1.3
B-2
BS-1-2
BS-1-3
BS-1-4
BS-2-1
BS-2-2
BS-2-4
BS-2-5
BS-2-6
BS-3-1
BS-3-2
BS-3-3
BS-3-4
CEF-1.1
CEF-1.2
CF-1.1
CF-1.2
CF-1.3
CP-1
CP-2
CP-3
CT-1
CU-1.3
CU-1.4
CWP-1
CWP-2
CWP-3
CWP-4
ECH-1
EEF-1.1
EEF-1.2
EEF-1.3
EEF-R.1
ERV-1
ERV-2
ERV-3
ET-1
EUH-1.1
EUH-1.2
EUH-1.3
EUH-1.6A
EUH-1.6B
EUH1.7
EUH1.8
EUH1.9
EWH-1
FC-1-1
FC-1-2
FC-1-3
FC-2-1
FC-2-2
FC-2-3
FC-3-1
FC-3-2
FC-3-3
FC-3-10
FC-3-11
FC-3-12
FC-3-13
FC-3-14
FC-3-15
FC-3-16
FC-3-17
FC-3-18
FCU-1.1
FCU-1.2
FCU-1.3
FCU-1.4
GWH-1
GWH-2
GWH-3
HP-1
HP-2
HP-3
HWP-1
HWP-1.1
HWP-2
HWS1.1
HWS1.2
HX-1
KEF-R.1
KEF-R.2
LEF-1.1
LEF-1.2
LEF-R.1
MAU-R.1
MAU-R.2
MEF-R.1
OHP-R.1
OHP-R.2
PEF-R.1
PEF-R.2
PEF-R.3
REF-R.2
REF-R.5
REF-R.6
REF-R.7
REF-R.8
RIV-R.1
RIV-R.2
RRV-R1
RTU-R.1
RTU-R.1 C
RTU-R.10
RTU-R.10 C
RTU-R.2
RTU-R.2 C
RTU-R.3
RTU-R.3 C
RTU-R.4
RTU-R.4 C
RTU-R.5
RTU-R.5 C
RTU-R.6
RTU-R.6 C
RTU-R.7
RTU-R.7 C
RTU-R.8
RTU-R.8 C
RTU-R.9
RTU-R.9 C
SP-1
SP-1
TEF-1.1
TEF-R.1
TEF-R.2
TEF-R.3
TEF-R.4
TEF-R.5
WEF-R.1

 

[Ryan D]

perfect! thank you very much

 

[Jeff Boyce]

One approach might be to treat this as two separate pieces of data, the text
portion and the numeric portion. If you could use a query to parse your
'string' to "aaaa" "####" ["aaaa"] (three separate fields), you could sort
each piece by the method appropriate to that piece.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP