Sorting by the last two characters

[RobInAZ]

I have a field that has entries like:
1234/SA/002-98
184/SAPPF0012-97
2234/7843/094-96
B23334/SSHSJU002-01
1234/SA/002-98
999/JHSU4/003-96

I would like to sort by the last two digits of the entry and then sort by the beginning of the entry excluding the last two digits all to be displayed on a form. What I have is a document control database. The last two digits are the year.

I would like to end up with results like:
2234/7843/094-96
999/JHSU4/003-96
184/SAPPF0012-97
1234/SA/002-98
56234/SA/002-98
B23334/SSHSJU002-01

Any help would be appreciated.

 

[shacket]

Create a field in your query with the Right function. It would look like this:

Year: Right([FieldName],2)

Then sort that field ascending.

 

[RobInAZ]

shacket,
Thank you I will give it a try tomorrow.

-Rob

 

[KeaganQuilty]

Why wouldn't the right() function work? The last two characters are always the last two characters.

Keagan Quilty
Kilbride, Newfoundland

 

[shacket]

Exactly. Unless I missed something (I actually read about the Right function to be sure that I understood it correctly), the Right function picks up the characters from the right side regardless of how long the string is. I even tested it on a field of mine and it worked on strings of various lengths.

Pat - are we missing something?

 

[RobInAZ]

Thank you all for your assistance and quick responses. I used shacket's suggestion and it worked perfect. Here's what the query looks like:

Field: Right([DocNum],2) DocNum
Table: Documents
Sort: Ascending Ascending
Show: X
Criteria:
Or:


Thank you,
Rob