Help Sorting OID Strings

[Patrick Maslanka]

I'm attempting to find a way to properly sort a field that contains an OID
string.

For those of you that dont know what an OID string is, it is a value that
describes index choices used to move down a series of nested indicies ....

The choice is a non negative INTEGER value ( ie 0, 1, 2, ... Infinity )

The format of the string is #.#.#.... IE the choice value is seporated by
periods.

The depth of the string ( how many numbers it contains ) is dynamic. IE it
could be 4 numbers or 40 numbers in the series.

Currently, using a field type of General will result in an OID string being
interpreted as a text string, and sorting is alpha numberic by character
position.

This is fine, unless the indicies can have choice values greater than 9.

In the case where such an index does exist, the text based alpha sort fails
to sort the strings correctly, because it will group the multi digit choices
with the character matched single digit choices ...

Example

1.2.6.1
1.2.6.10 <- this is out of order
1.2.6.3
1.2.6.7

The correct order should be:

1.2.6.1
1.2.6.3
1.2.6.7
1.2.6.10

-------------------

I am looking for a solution that would basically allow me to take this
general field type ( by redefinition or some other means ) and treat it as
multiple numbers. Level would be sorted as a standard number format.
Something like the following ...

1 2 3 4
| | | |
v v v v
1 . 3 . 6 . 1
1 . 3 . 6 . 3
1 . 3 . 6 . 7
1 . 3 . 6 . 10

In order to sort properly, if the depth was not equal between the strings,
it would have to assume .0.0.....0 at the end of the string to the matching
depth

Ive looked at custom number formats, but none of the definition starting
points seems to meet my needs.

I have considered dumping the string to a text file, and having a linked
table pick it back up, using the . as a delimiter, and describe the fields as
numberic. This would function for what I am attempting, if its possible to
sort based on more then 1 column of the database.

My only other option would be to programically convert the OID string into a
numeric value, or to place leading 0 characters into the string, forcing it
to be a specified width. Since i am not a macro programmer, im now sure how
to do this, but the algorithm would be

1) set new oid string =""
2) read characters and put into a temp string until character = "." or " "
3) count the number of characters in the temp string
4) insert "0" at end of new oid string 4 - count times
5) insert temp string at end of new oid string

This would have to be looped a number of times based on how deep into the
tree you wanted to insert leading 0's into the OID string, to force it to
sort properly.

--------------------------------

Any suggestion or insight into how to accomplish what im attempting would be
greatly appreciated ... I wish i knew of a way to define a custom field type
other then numeric, so i could actually define it and the sort properties of
the type, and then use it in all office applications.

Thnx in advance, for your assistance.

 

[Lance]

No amount of formatting will ever make 1.1.1.1.1 a numeric value.

You are going to have to do this as a string or break it out into multiple
number fields. And yes, you can sort a query on more than 1 field.

 

[Douglas J. Steele]

And you can construct a computed field in your query that combines the
multiple fields into one for display purposes, so for all intent and
purposes, no one knows it's any different.

 

[Ken Sheridan]

Multiple columns are not a practical option here as the depth of the
hierarchy is variable so you'd need columns to cope with the maximum
anticipated number of levels, and then what happens if you find you need to
go beyond that? The other option would be to have multiple rows in a related
table with each row having, in addition to a foreign key column, a column to
indicate its level in the hierarchy and another column the value. This is
relationally a better option in principle, but to rebuild the OID would
require that you iterate through the matching rows in the related table and
concatenate the values into a string expression. Nor would it overcome the
problem of sorting by OID without further formatting of the concatenated
string expression.

With your present structured string expression, however, you should be able
to return a sortable value with a function along the following lines:

Public Function ConvertOID(strOID As String) As String

Dim strConvertedOID As String
Dim intDotPos As Integer

intDotPos = InStr(strOID, ".")

Do While intDotPos > 0
strConvertedOID = strConvertedOID & _
Format(Left(strOID, intDotPos - 1), "000")
strOID = Mid(strOID, intDotPos + 1)
intDotPos = InStr(strOID, ".")
Loop

ConvertOID = strConvertedOID & Format(strOID, "000")

End Function

Paste this into a standard module and in a query sort the result set with:

ORDER BY ConvertOID(OID)

where OID is the name of the column in question.

The function would return 1 . 3 . 6 . 1 as:

001003006001

and 1 . 3 . 6 . 1 as:

001003006010

If further levels are present the return value of the function would simply
be extended ,
e.g. 1 . 3 . 6 . 1 . 4. 7 would return:

001003006001004007

As the return value is a string expression these should sort correctly. As
written the function allows for numbers up to 999 in each level of the
hierarchy, but you in the unlikely event of your needing to allow for more
you could do so simply by adding zeros to the format pattern, e.g. "00000" in
place of "000" to allow for up to 99999 per level.

You'll probably find there is a performance deficit when running the query
but hopefully it won't be unduly great.

Ken Sheridan
Stafford, England

 

[Patrick Maslanka]

Thank you for the programed function, this worked beautifully. And that you
all for your responses.