ms access db order by month asc issue

Question

In my database i have month name as january, february, march like that.

I represent field name is text. When i execute the select query with order by month field month, it will provide the output as february, january like that.

I know it is clear by the FORMAT or db field representation. Though i am new to the ms access so i don't know the correct format for this. Please help me.

Answer 1

Add an additional column to the table(s) with month names that have the month-number-in-year, or much better, don't store months as strings, instead store them as dates e.g. 1 August 2010, which will make this problem considerably easier to solve.

Answer 2

Store the months as integers as currently your rows are returned using alpabetic sorting (how should database know that these strings there are months?): April, August, February, January, March...

Answer 3

You will have to create your own custom function in a module that cnverts the value for you.

Something like

Public Function StrToMonth(strIn As String) As Integer
Dim arrMonth(12) As Variant
Dim i As Integer

arrMonth(0) = "January"
arrMonth(1) = "February"
arrMonth(2) = "March"
arrMonth(3) = "April"
arrMonth(4) = "May"
arrMonth(5) = "June"
arrMonth(6) = "July"
arrMonth(7) = "August"
arrMonth(8) = "September"
arrMonth(9) = "October"
arrMonth(10) = "November"
arrMonth(11) = "December"

For i = 0 To UBound(arrMonth) - 1
If strIn = arrMonth(i) Then
StrToMonth = i + 1
Exit Function
End If
Next i
End Function

And then you can use it in your queries like

SELECT Table1.MonthVal
FROM Table1
ORDER BY StrToMonth([MonthVal]);

Answer 4

You can convert the month to a date for the purpose of sorting:

SELECT MonthName
FROM SomeTable
ORDER BY CDate("1/" & [MonthName] & "/2010");

Answer 5

If you've decided it's inappropriate to store the months as actual dates (and it certainly could be -- I'm not criticizing that decision), you want to maximize performance by storing your data in a format that is most efficient.

That is most likely storing the month as an integer.

For display purposes, say in reports, or on a form, you can display the month name using format. It's a little tricky, as there's no direct conversion (where "MonthField" refers to the field where you're storing the month integer):

  Format(DateSerial(Year(Date()), MonthField, 1), "mmmm")

An alternative would be to have a table that maps a month integer to the month name, but that adds a join, and if the field can be blank, you'd have to have an outer join, and that's much more expensive performance-wise than an inner join.

Keep in mind that you'd use the Format() statement only in the presentation layer, i.e., as the controlsource of a control on a form or report. On forms you'd likely use a 2-column combo box for this purpose, so the user would see the month name, but the value stored would actually be the month integer. In that case, a table might make sense, but I'm not certain that it would have any real advantage over just typing in a value list -- it's not like this is volatile data, i.e., data that may need to be edited (which is the main reason you'd use a table in place of a value list)!

EDIT:

As @HansUp has pointed out, there's a MonthName() function in VBA (I didn't know it existed!), so that makes all the above way more complicated than it needs to be -- you could use that in the presentation layer without a need for a table or for the complicated Format() statement.