Sort a text in outline form 1.3; 1.3.1, 1.10.2

K

Ken Snell [MVP]

With great difficulty....especially if there is no "limit" on the number of
sublevels (.#).

You'd probably need to write a VBA function that will do all the work of
figuring out what the text field contains, and how to identify what it's
sort order should be. Then use that function in a calculated field in a
query and do your sorting on that calculated field.
 
T

Tom Wickerath

Hi Brad,

The easiest way, without writing a fairly lengthy VBA procedure, would be to
add a numeric SortOrder field to your table. Then base any query sorts on
that field. For example:

OutlineNo SortOrder
1.3 1
1.31 4
1.10 2
1.10.1 3

It could become rather painful to maintain the SortOrder field if you have
hundreds of records, and then you need to insert a new record somewhere in
the middle. That would require assigning a new sort order manually to each
OutlineNo. If you need to accomodate this need, then make the SortOrder field
a text field instead of numeric. The resulting data could then be entered
like this:

OutlineNo SortOrder
1.3 1a
1.31 4a
1.10 2a
1.10.1 2b

To use the SortOrder field for sorting, follow the example in this KB article:

How to Sort Alphanumeric Strings Based on Their Numeric Portions
http://support.microsoft.com/?id=209632

Now, if you need to enter a new OutlineNo, for example 1.10.2, you could
assign it a SortOrder value of 2c. The initial choice of SortOrder value will
determine how easy or difficult it will be to add additional Outline numbers
at a later date.


Tom
___________________________________

:

How do I sort a Text filed that is in outline form
1.3
1.31
1.10
1.10.1
 
D

Dirk Goldgar

Brad said:
How do I sort a Text filed that is in outline form
1.3
1.31
1.10
1.10.1
Click to expand...

Others have mentioned writing a VBA function to parse out the elements
of the string. As it happens, I wrote such a routine some time ago for
another poster. Here it is:

'----- start of code -----
Function fncHierarchicalElement( _
KeyString As Variant, _
Delimiter As String, _
ByVal ElementNo As Integer) _
As Variant

Dim strKey As String
Dim lngPos As Long
Dim intCount As Integer

If ElementNo < 1 Then
Err.Raise 5
End If
If Len(Delimiter) < 1 Then
Err.Raise 5
End If

fncHierarchicalElement = Null

If IsNull(KeyString) Then
Exit Function
End If

strKey = KeyString

ElementNo = ElementNo - 1

For intCount = 1 To ElementNo
lngPos = InStr(lngPos + 1, strKey, Delimiter, vbBinaryCompare)
If lngPos = 0 Then
If ElementNo = 0 Then
Exit For
Else
Exit Function
End If
End If
Next intCount

strKey = Mid$(strKey, lngPos + 1)
lngPos = InStr(1, strKey, Delimiter, vbBinaryCompare)
If lngPos > 0 Then
strKey = Left$(strKey, lngPos - 1)
End If

fncHierarchicalElement = strKey

End Function
'----- end of code -----

To retrieve, say, the second element of the string, you could have a
calculated field in a query like this:

Part2: fncHierarchicalElement([YourField], ".", 2)

If you need to sort numerically on this field, you'll need to convert
each element to a number; e.g.,

Part2: CInt(Nz(fncHierarchicalElement([YourField], ".", 2), 0))

You'd need to create a calculated field for each of the parts you
anticipate.
 
B

Brad

Dirk,
Thank you very much for the VBE code, it will help me a lot, I been trying
to do it with String commands in the Query. Again, Thank you.
Brad
 
T

Tom Wickerath

Hi Brad,

Another method passed on to my by Gunny is as follows (no VBA code required):

I create a calculated numeric field for each group of digits separated by
the dot, but the total number of groups must be known. For example:

OutlineNo PgSort ParaSort SectionSort
1.3 1 3 0
1.31 1 31 0
1.10 1 10 0
1.10.1 1 10 1

.... would need:

ORDER BY PgSort, ParaSort, SectionSort;


Tom
_______________________________________

:

Hi Brad,

The easiest way, without writing a fairly lengthy VBA procedure, would be to
add a numeric SortOrder field to your table. Then base any query sorts on
that field. For example:

OutlineNo SortOrder
1.3 1
1.31 4
1.10 2
1.10.1 3

It could become rather painful to maintain the SortOrder field if you have
hundreds of records, and then you need to insert a new record somewhere in
the middle. That would require assigning a new sort order manually to each
OutlineNo. If you need to accomodate this need, then make the SortOrder field
a text field instead of numeric. The resulting data could then be entered
like this:

OutlineNo SortOrder
1.3 1a
1.31 4a
1.10 2a
1.10.1 2b

To use the SortOrder field for sorting, follow the example in this KB article:

How to Sort Alphanumeric Strings Based on Their Numeric Portions
http://support.microsoft.com/?id=209632

Now, if you need to enter a new OutlineNo, for example 1.10.2, you could
assign it a SortOrder value of 2c. The initial choice of SortOrder value will
determine how easy or difficult it will be to add additional Outline numbers
at a later date.


Tom
___________________________________

:

How do I sort a Text filed that is in outline form
1.3
1.31
1.10
1.10.1
 
Top