Date format

[Chum]

I inherited a table with a field for Birthday. In the design view the data type for this field is Text. The mask is 99/99/00;0;_. Thus all the entries are of the form mm/dd/yy. When I change the mask to 99/99/0000;0;_ I can now enter the complete year. However it does not change the existing entries.

All the entries for mm/dd/yy is for the 20th century, that is, 19yy. Is there any way change the yy to 19yy for all the dates of the form mm/dd/yy?

 

[JANR]

Run an Update query like this (Try it on a Copy of the table)

UPDATE TableName SET DOB = DateSerial(Right([DOB],2),Left([DOB],2),Mid([DOB],4,2));

Change whats marked in red to your real tablename and fieldname.

If you can modify the table then I suggest you remove the inputmask AND change the FieldType property from text to Date/Time after you have sucsessfully updated your DOB field.

JR