Converting text to dates

[SamirNaenenjad]

I have an 8 byte text field that represents a date in YYYYMMDD format that I want to convert to a date type in Access. I can get the query to work from the query designer in Access, but I can't get it to work from an ADO connection object. Below is the query:

"UPDATE tblHerdImport SET [dtCalvingDate] = Mid([calving_date],5,2) & '/' & Right([calving_Date],2) & '/' & Left([Calving_Date],4) WHERE calving_date IS NOT NULL"

dtCalvingDate is a date and calving_date is an 8 character text field. My guess is that the string functions aren't allowed through ADO. I get a data type mismatch, which makes sense since I don't have the thing surrounded with '#', but the '#' don't work either.

Do any of you know of a solution? Is there an easier approach? I would rather not have to create a record set to make these updates, but maybe I have to.

Thanks in advance,
A.J.

 

[WildHare]

Umm..have you tried the CDATE function? Returns a DATE from any valid expression that resolves to a date.

CDATE("march 18&quot = 3/18/2002

cdate("2002-03-18&quot = 3/18/2002

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[OhBother30]

Try this

UPDATE tblHerdImport SET [dtCalvingDate] = cdate(Mid([calving_date],5,2) & '/' & Right([calving_Date],2) & '/' & Left([Calving_Date],4)) WHERE calving_date IS NOT NULL

Andrea

 

[SamirNaenenjad]

Thanks a million. cdate() works perfectly.