sort nonnumeric fields

[Simon]

Hello !!!
Can anyone explain how can I sort this type of fields -nonnumeric- : field#1
(chapter title type); field#2 (example txt 412), where txt is a unchanging
string text with 3 letters/or numbers, the rule for create field#2 is
repeating field1 without "." character and completing with "0" for 2'st or
3'st position:
Field#1; Field2
1 ; txt 100
1.1 ; txt 110
1.1.1; txt 111
.....
4; txt 400
4.1; txt 410
4.1.1; txt 411
THKS !

 

[Tom van Stiphout]

On Mon, 10 Aug 2009 01:50:01 -0700, Simon

I'm not sure I understand the question. You sort alphanumeric fields
the same way as numeric ones:
select * from myTable
order by myField2
(replace myObjectNames with yours)

Btw, don't use # or any other funny characters in field names.

-Tom.
Microsoft Access MVP

 

[NG]

Hi Simon,

to create Field 2, you can use a function as:

Public Function GetText(strFixedText As String, strNumber As String)

strNumber = Replace(strNumber, ".", "")
While Len(strNumber) < 3
strNumber = strNumber & "0"
Wend
GetText = strFixedText & " " & strNumber

End Function

Where strFixedText is the unchanging string text with 3 letters/or numbers
and the strNumber is the chapter number.

 

[Dale Fye]

Well, if all of the numbers will be less than 10, then you could do something
like:

SELECT [Field1], [Fieldx], [Fieldy]
FROM yourTable
ORDER BY Val(Replace([Field1], ".", ""))

the problem is that once you start getting to numbers where one or more of
the values gets to greater than 9, then you have problems. If that gets to
be the case, you might want to create a function that converts the text
values to a number; something like:

Public Function fnChapterToNumber(Chapter As String) As Long

Dim aChapter() As String
Dim intLoop As Integer

aChapter = Split(Chapter, ".")
For intLoop = LBound(aChapter) To 2
If intLoop > UBound(aChapter) Then
fnChapterToNumber = fnChapterToNumber & "00"
Else
fnChapterToNumber = fnChapterToNumber _
& Format(aChapter(intLoop), "00")
End If
Next

End Function

This would convert "4" to 40000, and "4.1" to 40100, and "4.1.1" to 40101
which you could then sort on.

 

[Dale Fye]

Simon,

Had not thought about it, but you could also do something like:

Left(Replace([Field1], ".", "") & "00", 3)

This would pull the periods out of the formatting and append zeros to the
right, then extract only the left most 3 characters. Again, this would not
work if your numbering scheme got to the point where any of the numbers was >
9.

 

[Simon]

Thks Tom,
In fact there are two questions -sorry, my fault !-: first/mainly : how sort
from field1 (# was just for example, I know, thks !) if I defined field1 as
text [10 ch] -numeric is incompatible with this format exl 4.1.1- in
ascendind order, as I mentioned.
Secondary: how to introduce automatically records on field2, based on field1
records. Thks Noelle !.

 

[Simon]

Thks Dale,

All the values will be under 10, but I need to generate field2 like this:
4.1 into txt 410, not 40100
4.1.1 into txt 411, not 40101
....
8.1.3 into txt 813, not 80103
9.9.9 (max) into txt 999, not 90909 or not in 090909

Well, if all of the numbers will be less than 10, then you could do something
like:

SELECT [Field1], [Fieldx], [Fieldy]
FROM yourTable
ORDER BY Val(Replace([Field1], ".", ""))

the problem is that once you start getting to numbers where one or more of
the values gets to greater than 9, then you have problems. If that gets to
be the case, you might want to create a function that converts the text
values to a number; something like:

Public Function fnChapterToNumber(Chapter As String) As Long

Dim aChapter() As String
Dim intLoop As Integer

aChapter = Split(Chapter, ".")
For intLoop = LBound(aChapter) To 2
If intLoop > UBound(aChapter) Then
fnChapterToNumber = fnChapterToNumber & "00"
Else
fnChapterToNumber = fnChapterToNumber _
& Format(aChapter(intLoop), "00")
End If
Next

End Function

This would convert "4" to 40000, and "4.1" to 40100, and "4.1.1" to 40101
which you could then sort on.

----
HTH
Dale

Hello !!!
Can anyone explain how can I sort this type of fields -nonnumeric- : field#1
(chapter title type); field#2 (example txt 412), where txt is a unchanging
string text with 3 letters/or numbers, the rule for create field#2 is
repeating field1 without "." character and completing with "0" for 2'st or
3'st position:
Field#1; Field2
1 ; txt 100
1.1 ; txt 110
1.1.1; txt 111
....
4; txt 400
4.1; txt 410
4.1.1; txt 411
THKS !

 

[Dale Fye]

My first recommendation indicates to use the Replace option, like Noella
recommended.

The code was for those situations where any of the numbers would be greater
than 9. You could modify the code so that it uses "0" instead of "00" to
work the way you want for single character chapter #'s.

----
HTH
Dale

Thks Dale,

All the values will be under 10, but I need to generate field2 like this:
4.1 into txt 410, not 40100
4.1.1 into txt 411, not 40101
...
8.1.3 into txt 813, not 80103
9.9.9 (max) into txt 999, not 90909 or not in 090909

Well, if all of the numbers will be less than 10, then you could do something
like:

SELECT [Field1], [Fieldx], [Fieldy]
FROM yourTable
ORDER BY Val(Replace([Field1], ".", ""))

the problem is that once you start getting to numbers where one or more of
the values gets to greater than 9, then you have problems. If that gets to
be the case, you might want to create a function that converts the text
values to a number; something like:

Public Function fnChapterToNumber(Chapter As String) As Long

Dim aChapter() As String
Dim intLoop As Integer

aChapter = Split(Chapter, ".")
For intLoop = LBound(aChapter) To 2
If intLoop > UBound(aChapter) Then
fnChapterToNumber = fnChapterToNumber & "00"
Else
fnChapterToNumber = fnChapterToNumber _
& Format(aChapter(intLoop), "00")
End If
Next

End Function

This would convert "4" to 40000, and "4.1" to 40100, and "4.1.1" to 40101
which you could then sort on.

----
HTH
Dale

Hello !!!
Can anyone explain how can I sort this type of fields -nonnumeric- : field#1
(chapter title type); field#2 (example txt 412), where txt is a unchanging
string text with 3 letters/or numbers, the rule for create field#2 is
repeating field1 without "." character and completing with "0" for 2'st or
3'st position:
Field#1; Field2
1 ; txt 100
1.1 ; txt 110
1.1.1; txt 111
....
4; txt 400
4.1; txt 410
4.1.1; txt 411
THKS !