Convert text to date

[MarcusAntonius]

Morning All,

In a table I have a text field (Meeting Date) which contains the date in the format: "Wednesday 22 March 2006"

How do I convert this to a date of format dd/mm/yyyy?

I have created another field of type date called MDate, in the same table, and have tried experimenting with an update query.

CDate(Left([Meeting Date],4) & "/" & Mid([Meeting Date],5,2) & "/" & Right([Meeting Date],2))

But this has not worked. (If someone could explain the significance of the numbers in the function that would be helpful also - Access help did not provide this information)

 

[Brianwarnock]

This is not so straight foreward as your text strings will vary in length

Monday 1 April 2006 or Thursday 31 December 2006, thus you need to use a combination of instr and len and mid and right functions to construct a txt field such as April 1, 2006 and then Datevalue will recognise this and give the date.

Brian

 

[allan57]

Try the following:-

Format(Mid([Meeting Date], InStr(1, [Meeting Date], " ")), "dd/mm/yy")

 

[MarcusAntonius]

Thank you Allan! It worked a treat.