Ordering the values of a field

[wjoc1]

Hi, I have a field in my table by which I want the records to be ordered by.
The values for this field are alphanumeric, something lke this:

C12/343/567
C12/76/234
C12/3390/65

The problem is that the user wants these ordered "numerically", i.e. they want that example above to be ordered in the following way:

C12/76/234
C12/343/567
C12/3390/65

However because I obviously have to have this field as a text field I cannot do this because Access (quite rightly) orders them alphabetically:

C12/3390/65
C12/343/567
C12/76/234

Is there any way I can parse the different parts of the string and use the Val() function in the ORDER BY statement in the sql of an underlying query or am I just screwed? (By the way the forward slashes are standard but the values can vary i.e. c12/1 and c12/234/56/1/23 ar both valid entries) No way, right!?

Thanks,

Liam

 

[pdx_man]

Is the C12 portion standard, at least in length?

Parse out the second string in a separate column in a query and set the sort on this column:

SortStr: CSng(Mid([field2],5,InStr(5,[field2],"/")-5))