Turning course code into date

[heyitshr]

We use Access to keep track of participants in courses. Our course codes are the date of the course plus the state abbreviation of where it is held, so 010919CA would be a course that is held January 9, 2019 in California. One of the things we use the data for is generating certificates of completion through a Word mail merge. Is there a way to have that course code changed into a date in a Word mail merge or do we need to just enter them in date form in Access? Thank for any suggestions.

[MRummell]

you can take the string access has the as the course code - pull out the date part and convert it to a date.

Left("010919CA", 6)
gives 010919
cdate(Format(Left("010919",2) & "/" & Mid("010919",3,2) & "/" & Right("010919",2)))
gives - 1/9/2019 as a date field

Taking that result and format as you like:

Format(#1/09/2019#,"long date")
gives Wednesday, January 9, 2019

and
Format(#1/09/2019#,"mmmm dd,yyyy")
gives - January 09,2019

I haven't tried to put this into a mail merge - but if you already have the code to make the merge then you can just add this bit in for the date.

[heyitshr]

This is the reason the word glee was invented. Thank you so much. I think I've probably put myself out of a job, but that was totally worth it. :)