Hello,
I have a large Access database with dates in the following format:

02AUG2011:00:00:00.000

They're saved as text and I was wondering how to convert this string into a date format?  I don't care about the time.  Thank you so much.  I appreciate your help!

---

Answer from Richard Rost:

You'll have to break apart the text string. I would create a QUERY with three calculated fields. Let's say your current Date/Time field is stored in a field called DT.

MyDay: Left(DT,2)
MyMonth: Mid(DT,3,3)
MyYear: Mid(DT,6,4)

Now you have three query fields that look like this

MyDay: 02
MyMonth: AUG
MyYear: 2011

Now you should be able to convert that to an ACTUAL Access Date/Time value using the CDATE function:

NewDate: CDate(MyDay & "/" & MyMonth & "/" & MyYear)

And that should do it. Hope this helps. For more help on Access date and time functions, see this tutorial:

There are many different things you can do with dates and times in Access.

You can use the DateAdd function to add or subtract date values. Want to add 6 months to a date but aren't sure which months between have 28 or 31 days? DateAdd takes care of that. It's also good for time values.

Please see the following FREE video tutorial on my web site for step-by-step instructions on how to use the DateAdd function:

http://599cd.com/tips/access/number-of-months-alive/?key=BlFAlEx


You can also use the DateDiff function to calculate the difference between two dates or times. This is useful for calculating the number of hours worked, someone's age, or even the number of school days in a year.

Here's a FREE video tutorial that discusses how to do some of that:

http://599cd.com/tips/access/dates-datediff-dateadd/?key=BlFAlEx


Now, one thing I get asked about ALL the time is how to figure out the number of days someone has to work, but EXCLUDE holidays like Christmas or Thanksgiving. Well, this is not so easy because some holidays (like Thanksgiving) fall on different calendar dates from year to year. In order to do this, you need to create a lookup table for your holidays, and use some advanced programming.

Unfortunately, I don't have a free tutorial that explains this in detail, but I do cover it in my Access 325 course, in lesson 4. We build a table to track student attendence, and we use a Holiday Table to know which days the students don't have to be in class. Here's the link:

http://599cd.com/site/courselist/access/access325/?key=BlFAlEx


I hope this helps.