convert month text (MAR) to month number (3)

[HLCruz via AccessMonster.com]

I am trying to work with a data set that unfortunately has spit out all the
dates in a text format - i.e. 03/01/2009 is MAR 3 2009.

Is there a way to convert that text date to an actual date format?

Any advice or suggestions are greatly appreciated.

Thanks

 

[Jerry Whittle]

Debug.Print CDate("MAR 3 2009") = 3/3/2009

One problem with the CDate function is that it will bomb out on things that
can't be evaluated as a date. Therefore you may want to use the IsDate
function first. Something like this will work in a query. If it can't be
evaluated as a date, it returns a bogus 1/1/1950 date. You could change this
to a more bogus date if that would help find them. Remember to change the
YourDateField to the actual name of your field.

TheDate: IIf(IsDate([YourDateField])=True,Cdate([YourDateField]),#1/1/1950#)