Custom Sort Query

[unvme]

Is there a way to sort a query by a partial field value?

Ex: I would like to sort by Project #, but only by a certain portion of the project number.

WA-1234
WA-5678
OR-2345
OR-6789

I want the projects to be sorted by just the number portion. So if I were to sort in ascending order, the result would be the following:

WA-1234
OR-2345
WA-5678
OR-6789

In essence, I want to ignore the WA- and OR- for each project number. Is this possible?


Thanks

 

[lespaul]

Maybe something like this:

ORDER BY SUBSTRING(PROJECTNUMBER, 4, 4)

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[unvme]

I don't quite understand what the last 2 constraints specify (4,4). What if I wanted to sort these project numbers by the 4-digit number and single letter extension (again disregarding the "WA-" and "OR-&quot:

WA-1234-A
WA-5678-B
OR-2345-A
OR-6789-B

Thanks

 

[unvme]

I figured it out. Just in case someone else may need this info, here's what I did:

I created a "Sort" column to my query.
SortOrder:Right([Project #],6)
(This new "Sort" column displays the last 6 place-holders in my Project #).

EX: PROJECT# SORT
WA-1234-A 1234-A
WA-1234-B 1234-B
OR-5678-A 5678-A
OR-5678-B 5678-B

Then I just sorted the query on the "Sort" column.

HTH