Help with query (make the months go in order)

[chedjs]

I have a little problem

I have an access database containing the following

Library Year Month Visits
Lenzie 1999 Dec 1095
Lenzie 1999 Feb 789
Lenzie 1999 Mar 1293
Lenzie 1999 Apr 1526
Lenzie 1999 May 1231
Lenzie 1999 Jun 1171
Lenzie 1999 Jul 1206
Lenzie 1999 Aug 1287
Lenzie 1999 Sep 1206
Lenzie 1999 Jan 1451
Lenzie 1999 Nov 1706
Lenzie 1999 Oct 1414
Lenzie 2000 Oct 956
Lenzie 2000 Jan 1113
Lenzie 2000 Feb 858
Lenzie 2000 Mar 1110
Lenzie 2000 Apr 1198
Lenzie 2000 May 1203
Lenzie 2000 Jun 1105
Lenzie 2000 Jul 1152
Lenzie 2000 Aug 916
Lenzie 2000 Sep 985

As you can see the months are in order alphabetically. How can I fix my query so the months can be in the right order. Jan,Feb,Mar etc?

I imagine it is a simple procedure but I am not sure how. PLEASE HELP

Derek

 

[stopher]

Ideally you should store your months as numbers or even better store the month/year as a date datatype. However, the following expression can be used in a query or report to be used as a sort field:

MonthSortKey: Month([myMonth] & " 1, 2000")

Note that I've called the month field myMonth to distinguish it from the function MONTH. You should avoid naming fields Month and Year. However, using [Month] instead of [myMonth] will still work.

I chose 1, 2000 arbitrarily but it doesn't matter what date is use as we're only interested in converting the month. Note the space before the 1.

hth
Stopher

 

[grnzbra]

I'm not sure if there is a function that would convert text months to numeric. If you find it use that, otherwise create your own using a Select Case statement. (you need the month in two digit format.) It will have the month and year as inputs. Inside the function, after the select case statement, concatinate the year and the month so that you have "yyyymm". Add a calculated field to your query set to this function and sort on this field.

 

[FoFa]

Try using SWITCH to create a sort field (you don't have to display it).
SWITCH("jan",1,"feb",2,"mar",3...)