Sort Alphanumeric

[jsic1210]

Hello all,

I want to know if there's a way to sort alphanumeric fields where the number of digits aren't always the same. Example: I have the following: 2a, 10a, 3a. Currently, it sorts: 10a, 2a, 3a. Is there a way to get it to sort 2a, 3a, 10a without adding a 0 before 2a and 3a?

 

[gemma-the-husky]

if the number is always at the start it becomes easy.

have a field in your query

expn1: val(fieldname)

then sort ascending based on this field, plus the real fieldname.

val extracts a leading number. if the number is not at the start, val will return zero, so it won't work.

 

[jsic1210]

Okay, I didn't know if that would work on mixed fields. How would I do a custom sort on a form, though?

 

[jsic1210]

I created a separate field to pull the Val(), but it's still not quite working the way I want. For example, in section number, I have values of 2a, 2c, Exhibit A. I want them in that order. If I sort by Val([Section Number]), the order comes in as Exhibit A, 2c, 2a. I tried it descending, but it comes in as 2c, 2a, Exhibit A. Does anyone have any suggestions?

 

[Brianwarnock]

How about if you make your separate field

IIf(Val([Section Number])=0,9999,Val([Section Number])

then all of the alpha only will be at the end


Brian

 

[jsic1210]

The iif(val) express worked once I added a second layer to the sort. Thanks!