Sort alphanumeric field sequentially

Question

I wish to sort the data in my Report based on a field called Survey Numbers. The survey numbers are alphanumeric:

A1, A2, B1, B2, C1, C2......  etc

The letters go from A-M, and within each letter there could be from 1 up to 140.

When I do a regular sort it does not order the numbers correctly.  For example it sorts A11 before A2. I understand this is because it's a text field and doesn't recognize the numbers. I also understand that I need to add an expression to my underlying query, but so far in my searches I have not found an expression that doesn't return syntax errors.

I have been searching for a clearly explained answer for hours (I am not an advanced user and do not know how to write code).  Please can someone explain how to do this in a way that is simple and understable?  And provide me with the exact expression I need using my field 'Survey Numbers'.   thanks!

Answer 1

Sort first on the expression:

    Left([Survey Numbers],1)

and then on the expression:

    Val(Mid([Survey Numbers],2))

The first will sort by the initial letter, and then within that the second will sort by the value of the remainder of the string.

Answer 2

Thank you!  This worked a charm :)

So appreciate your time

Answer 3

I'd put in a calculated field:

SortNum: Val(Mid([Survey Numbers], 2))

and sort first by [Survey Numbers] - to sort by the letter portion - and secondly by SortNum. 

The first sort term will put all the A's together, then the B's and so on; the Val() function will sort the remainder of the field in numeric order. 

You will get an error message if the Survey Number field is NULL or doesn't have a number portion - you can either use a criterion of IS NOT NULL or a slightly more complicated expression. Post back if that's needed.

Answer 4

Hi,

to sort in a report, in report design view with right click select order and grouping

Ciao Mimmo