Converting month to date format in access

Question

I have an access column which has values like May, May-June, November, January-February etc.

Requirement is that 1) If only one month is there, I should create a transformation with a date column pointing to the last date of that month and the year would be the current year. Eg May would be 5/30/2015, January would be 1/31/2015 2) If it is having two months, then second month must be taken and the same logic as in Point 1 should be implemented. Eg May-June would be 6/31/2015, January-February would be 2/28/2015.

My first preference would be without using VBA code.

Please help.

Answer 1

You can use a one-liner in a function:

Public Function GetDateFromMonth(ByVal Months As String) As Date

    Dim Ultimo As Date

    Ultimo = DateAdd("d", -1, DateAdd("m", 1 + UBound(Split(Months, "-")), CDate(Split(Months, "-")(0) & "/1")))        
    GetDateFromMonth = Ultimo

End Function

Answer 2

Split the string on the "-", use the second month, add one to that month to get the next month, then take the first day of that month and subtract one from it. For example Jan-Feb : build 3/1/2015 and then subtract 1 day to get the last day of Feb.

Answer 3

Make a table that holds a list of Months:

    MonthName   MonthNumber
January 1
February    2
March   3
April   4
May 5
June    6
July    7
August  8
September   9
October 10
November    11
December    12

Create this query:

   SELECT InputData.Months, DateSerial(Year(Date()),[MonthNumber]+1,0) AS ReqdDate
FROM InputData, Months
WHERE (((IIf(InStr([Months],'-')=0,[Months],Mid([Months],InStr([Months],'-')+1)))=[MonthName]));

That's it. Output looks like this:

    Months  ReqdDate
May 31/05/2015
May-June    30/06/2015
November    30/11/2015
January-February    28/02/2015
March   31/03/2015
April-September 30/09/2015

Sorry about formatting - noob. I'm in Eu so my dates look odd to you, but you get the idea; you can make them US dates in a moment I'm sure. You can use the bits of this you need in your code in two secs.

If you don't like the lookup table for month number, I'm sure you can use some format function to turn the month name into a month number.