Field for extracting part of field to be used for sorting -- in query - or table?

[louise]

Hello,
I have a course code that includes letters followed by numbers, so a text field in my table.

However, I need to be able to sort the field by the letters and then the numbers. And, I do not want the user entering the information to have to add leading zeroes to numeric part. For example, codes may be FSS-1, FSS-11, FS-1 FS-11

In a query I am able to extract the numeric part, sort by that calculated field, and the course codes are then in the correct order. (sorry for the font...)
Extract: Val(((Mid([CourseCode],InStrRev([CourseCode],"-")+1))))

But, using this in the query would mean I would have to include this calculated field in all my queries -- it seems impractical. Can it be put into the table? Would it recalculate in the table if changes were made? How would I do that? (And is this a normalization non-no?)



OR any other suggestions for sorting would be welcome!

Thanks

[pbaldy]

I haven't used it but 2013 has a new calculated field data type.