Order by letters and then numbers

[kazaccess]

I have a column containing an id that consists of the first two letters of a weekday followed by an incrementing number. For example, for Monday, I have "MoA1" "MoA2" "MoA3" ... "MoA11".

The problem is that when I sort my list, it is ordering it: "MoA1" "MoA10" "MoA11" "MoA2" "MoA3" etc. Currently, my order by property is set to

MID(TABLENAME.SORTFIELDNAME, 3, LEN(TABLENAME.SORTFIELDNAME))

Any help would be greatly appreciated.

 

[plog]

Discrete pieces of data should be stored distinctly. I would change your table structure so that each piece of information currently in that field (the day and the number) is in its own field.

Your issue is a symptom of a poor structure. Once you have the data stored properly in two fields of the proper datatype, you can then sort by the day field first, then the numeric field.

Follow up issue: Do you want Thursday to come after Wednesday? Because when you sort 'We' and 'Th', 'Th' is going to come first. This is what the days of the week look like sorted by text:

Friday
Monday
Saturday
Sunday
Thursday
Tuesday
Wednesday

 

[kazaccess]

I've been able to order my records chronologically by weekday, so that's not an issue right now. Unfortunately, I've been tasked with fixing this database, and I don't have the time to change the structure. If I had created it, I would have made separate fields for each piece, but the query is used in so many other forms and reports that I don't have the time to do so.

 

[JHB]

Where do you want to use the sorted list, in a form, query or?
Would it mind you anything if it looks like "MoA01" "MoA02" "MoA03" ... "MoA11"?
If not then you could run an update query on your table data once and afterwards change the code where the two letters of a weekday followed by an incrementing number is inserting in your table.
But ofcause you can also add the missing 0 each time the query runs.