Update DateTime field from string

[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

 

[Golom]

You might

UPDATE tblPet SET dateOfBirth = CDATE([red]Trim$([/red]MID(AGE,4)[red])[/red]) WHERE LEFT(LCASE(AGE), 3)='dob'

But it should work the way you have it.

What results are you getting when you run it?

 

[BlindPete]

LOL naturally I agree, it should work. ;-) The ASP server uses a MS Jet ODBC connection.

Error Type:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.

I'll try your method here shortly.

-Pete

 

[BlindPete]

Bummer, same error. This is one off maintenance query. I guess just dlaod the dbase and execute the update in the Access environment on local host.

I suspect that the are conversion errors on the string field, that are canceling the entire transaction.

I guess I could break into 2, identify the records, update 1 by 1 and find the offenders.

-Pete