Date conversion gone wrong

[Keith]

I inherited a db where dates were stored in a text field. I added a temporary field to the table of Date data type. I ran an update query to update the temporary date field to the date value of the text date field. I then deleted the text date field and remamed the temporary date field to that of the text date field. I now find that for dates from 1900 to about 1930 are shown as 2000 to 2030. This is affecting 600 records. Is there an easy way to reduce the date by 100 years?

 

[pr2-eugin]

Do you have a backup copy of the DB before you performed the DELETE?? Is only 600 records affected? are you sure?

 

[Keith]

I do have a backup and yes its only date before about 1930 that are affected.

 

[pr2-eugin]

What was the Query you used to perform the update?

 

[Keith]

DateValue([TextDateField])

 

[pr2-eugin]

Instead of DateValue try CDate([TextDateField])..

 

[Keith]

Thanks for your help. I solved it by using a select query to find all the incorect dates the updated them using DateAdd("yyyy",-100,[DateOfBirth])