How to Convert MonthName to Value(Microsoft access 2003)

Hi All,

How to Convert MonthName Like September, October to value for enabling sorting. I used Format$(Exp,"mm") but i can't sort the record .

Thanks you


PS: Microsoft access 2003

Not sure what you mean. Do you want the month number?

Use this:
Month(CDate("1 " & strMonth & " 2000"))

Or even shorter:
Month(CDate("1 " & strMonth))

where strMonth is the name of month such as September, October.

Note that MonthName() is a VB built-in function that convert a number from 1 to 12 to the name of month:
MonthName(month[, abbreviate])

Try this

vb Code:

Private Function GetMonthNumber(pstrMonthName As String) As Integer
Dim i As Long
For i = 1 To 12
  If pstrMonthName = MonthName(i) Then
     GetMonthNumber = i
     Exit Function
  End If
Next
End Function
 
Private Sub Command1_Click()
Dim intMonth As Integer
intMonth = GetMonthNumber("October")
End Sub

@Hack, why you have to create a UDF while you can use a simple thing like this to return the Month number:

Month(CDate("1 " & strMonth))

And your Private function cannot be used in a query.