sorting

[octatyan]

I have a problem with sorting.
My form displays memos one at a time. Each Memo has unique # which uses the following forumula : IS(yr)-1285-(memo #)
ex. IS98-1285-001
IS98-1285-002
IS98-1285-003

I have memos from 97 to today.
The memos are all sorted by the above unique #.

Currently, they are sorted in a strange order due to using only the 2 digit yr. It goes, year 00 first, then 01, 02,03,97,98,99.

How do I sort it so that the oldest memo shows first (97) and the most recent memo shows last (03). (I'll have the program automatically move to the most recent memo so that it'll be one record away from a new blank record)

I know it would have been easier to use the four digit year, but there are now too many memos to manually go back and change them all. Is there code I can use to accomplish what I'm trying to do?


Thanks for any help. It is much appreciated!!!

 

[dcx693]

If your table's recordset is set to a tablename, I suggest you create a query to feed into the form. If you're already using a query, then just create a calculated column that you can sort on, but not display in your form.

I didn't get straight from your post whether or not the memo is being calculated or not. In any case, you need to concern yourself with the 2-digit yr part and the unique number at the end. Everything else about the fields is the same.

Create a calculated field in your query. To get them to sort correctly, do something like adding 100 to any 2-digit yr that is less than 50 and leaving the 2-digit yrs that are greater than 50 alone. That wll transform the number this way:
02 --> 102
98 --> 98
50 is just an arbitrary number, you can adjust it as needed. Sort on that numeric field first. Then sort on the unique number field second.

You can unclick the "Show" checkbox since you don't need to show these fields, you just want to sort on them.