CODEWITHSUNDEEP
datetimetype-conversionms-access-2013
Yousuf Sultan
Yousuf Sultan

Reputation: 3257

Converting String to Date format in MS Access

I have field which show the month and year in a string format. How can I convert it into MMYYYY format in Microsoft Access?

The field values are as follows:

Period Code
M01-15
M02-15
M03-15
M04-15
M05-15

The above values are sent by the client.

I want to get the values in any date format preferably in MMYYYY.

The result should be as:

Period Code
012015
022015
032015
042015
052015

Can anyone suggest me how can I get the desired result?

Upvotes: 0

Views: 176

Answers (1)

Gustav
Gustav

Reputation: 55841

You can use DateSerial and Format:

MDate = "M03-15"
MonthYear = Format(DateSerial(Mid(MDate, 5), Mid(MDate, 2, 2), 1), "mmyyyy")
' Returns: 032015

Or brute force with Replace:

MDate = "M04-15"
MonthYear = Replace(Mid(MDate, 2), "-", "20")
' Returns: 042015

Upvotes: 1

Related Questions