Pull numbers from text field and sort 1

[bbwrede]

Hi,
I have a query that displays the following data in a CR:

map #
11B1-10
11B1-23
11B1-9
11B1-9A
11B1-9ZZ

I need to strip off the numeric part of the field that is after the "-" and sort. For example, I need to display the rows in the following order:

11B1-9
11B1-9A
11B1-9ZZ
11B1-10
11B1-23

It does not matter what order the "9"'s are in 9ZZ could come before 9A. I just need the numeric portion in numeric order.

How can I do this in CR 9?

Thanks!

 

[lbass]

Try using:

val(mid({table.field},instr({table.field},"-")+1))

-LB

 

[bbwrede]

But how do I sort my list by this value before printing it out? I can't use this as part of my query. Is there a way to sort in CR after you have gotten your SQL results?
Thanks so much.

 

[lbass]

Create this formula in the formula expert and then add this formula as your sort field. Go to report->sort records and add it there. You probably should use the formula only for the sort, and use your actual field for display on the report.

-LB