Format existing columns of data

[AbeA]

I have an old table that has many, many records. One of the columns lists the Date of Birth. Here's my problem:

Users have entered data into this column as "71462" This representing July 14, 1962. Is there a way to automatically format all data in this column so that it is more palatable - e.g. 07/14/1962?

Changing the column Data Type to "Short Date" in Design View would delete all existing data in that column, so this is not an option.

Thanks for your help.

 

[pbaldy]

There needs to be a consistent format. Given what you've shown, it appears that they could enter 11107. Is that January 11 or November 1? There's no way to tell. It would be possible if they always entered 2 digits for the day, even if the first was zero.

 

[AbeA]

Thanks pbaldy. I have just modified the database so that all D.O.B fields in the database contain atleast 6-digits (e.g. 71562 is 071562, or mmddyy) How would I go about applying the format I need (07/15/62) to each record at this point?

Thanks again.

 

[pbaldy]

At least 6 digits? What would more represent? In any case, you can use the DateSerial function to convert the string to a usable date, using the Left, Right and Mid functions on your string to provide each of the 3 arguments. Look in VBA Help for more info on those functions.