VBA to convert a Month to Number

[bar891]

I have a table that has just Months in a column. As in January, February......
I want to convert these to Numbers as in 1-12 in VBA. I tried all the DatePart in every combination but can't seem to get the results.

 

[plog]

Try this expression:

MonthNumber: Month(CDate([MonthName] & "/1/2014"))

You didn't tell me the name of the field the month data is in, so I used [MonthName]

 

[namliam]

Assuming they are proper month names...

Month("01/" & YourMonth & "/2014)

Otherwize the easiest way is to make a new table, add the 12 months, with the appropriate numbers in a second column and join them in a query....

In VBA, if you are married to that idea....

Function fnRenumberMonth(MonthName as string) as integer
select 
   case monthname = "January" 
      fnRenumberMonth = 1
   case .... etc...
end select
end function

Or something along those lines.

 

[bar891]

Thanks Plog works fine.... Now i have another field with Years, how do i work that into that expression?

 

[namliam]

Try this expression:

MonthNumber: Month(CDate([MonthName] & "/1/2014"))

MonthNumber: Month(CDate([MonthName] & "/1/" & YourYearField))

 

[bar891]

Thanks guys...worked a treat!