month string to numerical month

[pb21]

Hi
I have a field that is text which holds the month in form "oct" etc. I need to pass the numerical value of that month to a function so how do I create an expression that will change it (in this instance) to 09.

kind regards in advance.
peter

 

[neileg]

Several ways. The one I would use is to create a two field table with the months in as text and the equivalent number. Join this table to your original table in a query on the text month field and retrieve the month number.

 

[boblarson]

You can use this (I created for VB6 - I don't know that you need the lowercase ones, but in VB6 it is case-sensitive so I trapped for all possibilities).

Public Function RetMONumber(strMonth As String) As String
    Select Case strMonth
    Case "Jan", "jan", "January", "january", "Jan.", "jan."
        RetMONumber = "01"
    Case "Feb", "feb", "February", "february", "Feb.", "feb."
        RetMONumber = "02"
    Case "Mar", "mar", "March", "march", "Mar.", "mar."
        RetMONumber = "03"
    Case "Apr", "apr", "April", "april", "Apr.", "apr."
        RetMONumber = "04"
    Case "May", "may"
        RetMONumber = "05"
    Case "Jun", "jun", "June", "jun", "Jun.", "jun."
        RetMONumber = "06"
    Case "Jul", "jul", "July", "jul", "Jul.", "jul."
        RetMONumber = "07"
    Case "Aug", "aug", "August", "august", "Aug.", "aug."
        RetMONumber = "08"
    Case "Sep", "sep", "September", "september", "Sept", "sept"
        RetMONumber = "09"
    Case "Oct", "oct", "October", "october", "Oct.", "oct."
        RetMONumber = "10"
    Case "Nov", "nov", "November", "november", "Nov.", "nov."
        RetMONumber = "11"
    Case "Dec", "dec", "December", "december", "Dec.", "dec."
        RetMONumber = "12"
    Case Else
        MsgBox "Not a valid month name or abbreviation!", vbExclamation, "Invalid Entry"
End Select
        Exit Function
End Function

 

[Bodisathva]

if the text box is named 'txtDate', try:

DatePart("m",CDate("1-" & txtDate))

 

[bvan]

The datepart function will not understand a date that shows month only. You will need to add a space and a 1 to the string to run the function. Again, assuming the field is called txtdate, use the following:

datepart("m",datevalue([txtdate] & " 1"))

 

[Bodisathva]

The datepart function will not understand a date that shows month only.

perhaps you should look again.