sorting value lists with text and numbers

[Mariano]

I have a value list that references a field that displays all the floors in a building....

Most of the floor fields are numeric, but there are values in there that are actually text, (for example - roof,Basement, Bulkhead)

If i make the floor field a text field then the floors arent listed in numerical order (for example - floors 11,12,13,etc come before floor 2), but if i put it as a number field then roof, basement, bulkhead, show up as "?".

Is there anyway to do a custom sort order, or any way around this??? I would hate for my value list to not be in order.

[mr_vodka]

One way to do it is to have a table for your value list with a field that has it numbered in the order that that you want to have it.

Then you can make it so that it shows an additional field and sorted by the number field.

[mf]

Is there a way to order the list without having to show the order number (just show the second field)?

(the screen shown above does not have the check box "Show values only from second field", if checked, the sort is forced to be on the second field)

I am looking for a way to NOT order alphabetically and I don't like the list with the numbering... (my second field contains numbers but they are not significant in helping to sort - it makes adding the order number in front of it confusing)

Hoping to know if any of you have found a way to go around this restriction.

Edited February 15, 2006 by Guest