Sorting: Text with embedded number

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
 
B

Bas Cost Budde

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.
 
G

Gary Schuldt

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
 
B

Bas Cost Budde

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
 
G

Gary Schuldt

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...
 
Top