SQL Update of DATETIME Field

[BlindPete]

Hello,

I am using ASP classic with an access dbase and I'm having trouble writing and UPDATE query for the DateTime field.

I have a varchar field with information like this:
"dob 01/01/2009" I want to update another field that is actually a datetime field. but I can't figure out how.

I've tried many variations of below:

UPDATE tblPet SET dateOfBirth = CDATE(MID(AGE,4,100)) WHERE LEFT(LCASE(AGE), 3)='dob'

Any ideas?

-Pete
Games the old fashion way with Dice, Paper and Pencils!

 

[tsuji]

>UPDATE tblPet SET dateOfBirth = CDATE(MID(AGE,4,100)) WHERE LEFT(LCASE(AGE), 3)='dob'
This sql should work for access, if all the data in the field AGE is what you described. (length 100 is not really needed. Leave it out will output every character from character 4 to the rest of the data. But, it won't harm neither the working of the sql.) What is the error? How do you use it?