I have some existing records that need to be edited. About 2,500 of them. The field is for Date of Birth and someone set it up as a text field with the dates entered as mm/dd/yy (No actual format). I need to insert a value of 19 to create a format of mm/dd/yyyy. For example, 01/06/63 needs to be 01/06/1963. After I fix this issue, I will create a new table with the proper Date/Time data type and format and import the records. (The new table is being created because additional fields are being added.) I can't seem to find a string manipulation function that will accomplish this task. Any suggestions?
I would just change the field type for the field. Access will automatically try to convert it to a date using CDate. If you are concerned that it won't convert properly make a copy of the table or make a copy of the database before you try it.