Sorting: Text with embedded number

  • Thread starter Thread starter Gary Schuldt
  • Start date Start date
G

Gary Schuldt

There must be an easy solution to this problem, which I've seen before but
don't know a solution:

I have a text field which represents a geographical sector Sector followed
by a sequence number. Examples are: NE-1, SE-10, NW-6, etc.

When I sort Sector ascending in a query, of course the order comes out:

NE-1
NE-10
NE-11
NE-2
etc.

when I want it to come out

NE-1
NE-2
NE-10
NE-11
etc.

What's the easiest way to get the result I want?

Thanks.

Gary Schuldt
 
Gary said:
There must be an easy solution to this problem, which I've seen before but
don't know a solution:

I have a text field which represents a geographical sector Sector followed
by a sequence number. Examples are: NE-1, SE-10, NW-6, etc.

What's the easiest way to get the result I want?
Click to expand...
If the 'NE' part is fixed in length, you can add an expression to your
query:
val(mid(sector,4))

and sort on this (if you forget the Val, the result doesn't change from
your problem)

If the text part is of varying length, it will get difficult.
 
Sounds promising. You mean, replace the sort on Sector with two expressions
and sort on them--i.e., primary sort on 1st two characters of Sector and a
secondary on the numeric representation of the last part
(val(mid(sector,4)) )?

Gary
 
Gary said:
Sounds promising. You mean, replace the sort on Sector with two expressions
and sort on them--i.e., primary sort on 1st two characters of Sector and a
secondary on the numeric representation of the last part
(val(mid(sector,4)) )?
Click to expand...

Yes, indeed. The first expression you don't need to change--it will sort
on the first characters anyway.
-bcb
 
Right! Thanks for the help!

Gary

Bas Cost Budde said:
Yes, indeed. The first expression you don't need to change--it will sort
on the first characters anyway.
-bcb
Click to expand...
 
Back
Top