type conversion failure with DateSerial

[odadgari]

I have an update query:

Query: DateExtractor
--------------------------------
Type: update query
Field: ProductionDate
Table: Production
Update To: DateSerial("2000"+Val(Mid(

;1;2));"Mid([Code];3;2)";"Mid([Code];5;2)")

Field [Code] is a Text field and ProductionDate is Date/Time type, with short date format.

As I run query, there's an error that says "Microsoft Access didn't update 99 field(s) due to DateSerial,..."

Where is my mistake? Please Help!

 

[Galaxiom]

Are any of the input values Null?

 

[odadgari]

Are any of the input values Null?

No. All the values in field

 are 10 character (digit) text type!

 

[Galaxiom]

The problem is the double quotes. You ar feeding stings to DateSerial when its wants numbers.

Moreover your strings are things like "Mid(

;3;2)" which can't be cast to numbers.

DateSerial(2000 + Val(Mid([Code];1;2)); Mid([Code];3;2); Mid([Code];5;2))

 

[odadgari]

The problem is the double quotes. You ar feeding stings to DateSerial when its wants numbers.

Moreover your strings are things like "Mid(

;3;2)" which can't be cast to numbers.

DateSerial(2000 + Val(Mid([Code];1;2)); Mid([Code];3;2); Mid([Code];5;2))[/QUOTE]

Thanks a lot! It seems it's solved.