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

 

[Pat Hartman]

I don't think the right() function will work since the last two digits are not always in the same place. I think you'll need the len() and mid() functions.

DocYear:Mid(DocID,Len(DocID)-1,2)

Use the len() function to determine how many characters the field actually contains. Subtract 1 to back up to pick up the last two characters. So the length of the first DocId is 14 and the above expression will extract positions 13 and 14.

 

[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

 

[Pat Hartman]

The problem I was envisioning is actually caused by one of my primary data sources which is an SAP manufacturing application. They store all data as fixed length fields with trailing spaces. So, if the field length is 10 but the field only contains 8 significant characters, the last two are spaces. And, 2 spaces are what you would get if you used the Right() function. Therefore, in my defensive programming mode, I tend to use the method I posted which always works, rather than relying on Right() which in the case of my SAP tables, does not

.

I did omit an important piece though -
DocYear:Mid(DocID,Len(Trim(DocID))-1,2)

The Trim() gets rid of any leading or trailing spaces. If you can rely on your data, Right() is certainly easier.