Complex sorting of text field

[Guest]

I have a text field that needs to be sorted alphabetically. However, it has
a hodge-podge of mixed cases and roman numerals. For example -

003 (A) (3) (c) (iv)
003 (a) (3) (c) (v)

My ideal solution is that the first set of numbering would be sorted by
their case sensitivity. The second column is a slam dunk, the third column
needs to consider case sensitivity also. The fourth column is the real
trouble maker. The values are text, but we look at these as having numeric
values.

This text you see above is one field and can not be broken into multiple
fields.

All you ideas and suggestions are greatly appreciated.

 

[Steve Schapel]

Seth,

As regards "This text you see above is one field and can not be broken
into multiple fields", as far as I can see you really have no choice
here. You will have to split the string so each element can be sorted
separately. You will be able to do this in a query, using string
manipulation functions such as InStr(), Mid(), etc, or a user defined
function.

As regards the slam dunk... only if the number never goes beyond 9.

As regards the roman numerals, I would be inclined to make a table, two
fields, one being all the possible roman numeral values you may use in
your actual data, and the other being the corresponding numerical value.
Then you can join this table to the roman numeral element in your
other query, and use the numerical value for the sorting. Hope you
understand what I mean.