How to sort graduation dates in the form of "SS77", "FS09", "FS11", etc.

[Spartanhockey]

I have a list of graduation dates that are scattered. For example:

SS67
SS78
US99
FS01
FS11
SS77
US69

I want them to sort by the numeric portion first. So I want it to look like this

SS67
US69
SS77
SS78
US99
FS01
FS11

I have the data in access. Is there anyway I can create a query that will let me find all dates that are: US67< X < FS01

 

[Ruddles]

You need a subquery which extracts the numeric part of the year from the four-character version of the date (the bit inside the parentheses), then you pass the data with its newely-created two-digit date to a query which sorts it. Try this:-

SELECT [COLOR=red][B]graddate[/B][/COLOR] FROM 
    (SELECT [COLOR=red][B]graddate[/B][/COLOR], MID([COLOR=red][B]graddate[/B][/COLOR],3,2) AS gradyear FROM [COLOR=red][B]tblGradDates[/B][/COLOR]) 
ORDER BY gradyear;

Change the bits in red to match your table and field names.

(This should be in the Microsoft Access forum really.)

 

[Ruddles]

Minor correction - I left the year selection criteria out:-

SELECT [COLOR=red][B]graddate[/B][/COLOR] FROM 
    (SELECT [COLOR=red][B]graddate[/B][/COLOR], MID([COLOR=red][B]graddate[/B][/COLOR],3,2) AS gradyear FROM [COLOR=red][B]tblGradDates[/B][/COLOR])
WHERE gradyear > 67 OR gradyear < 01
ORDER BY gradyear;