Need help converting text field to date field 1

[djgolds]

I'm going to make 1 last effort to see if there's a way to do this. Thanks to those that have helped so far!

I have a table tblTest2 where the BirthDate field is entered as a text field and I want to convert it to a date field. What I've tried so far is creating a query from tblTest2 with the BirthDate field (still text) and a new field named newDOB as a date field, formatted to short date. I've then run an update query with the criteria in the Update to field under newDOB as
DateValue(Left([BirthDate],2) & "/" & Mid([BirthDate],3,2) & "/" & Right([BirthDate],4))

When I go to datasheet view after running the update query, it changes the first record in newDOB only and leaves the rest of the entries under newDOB blank. Any suggestions on how I get each of the records to update? An example of how the text records under BirthDate are entered is 71355 for 7/13/55. The preceding zero before 7 does not appear. Any suggestions would be appreciated. Thanks!

 

[BMeek]

Does the original data for the birthday field have preceeding zeros? In your sample there was no zero at the start - 71355 for 7/13/55. You might try to add the leading zero to a sample record and see if it works. What about the day portion, do you want leading zeros for a date like 12/05/58 or just for the month?
Bryan Meek
bmeek@pacbell.net

http://www.handtech.com/meekent

A "Solution Provider" in your corner gives you the personal attention you need to find the right technology solutions for your business.

 

[djgolds]

The BirthDate field actually started out as a number field. The preceding 0's before month and date were entered, but because it was a number field, it didn't leave the 0 in place for the month. I then changed it to a text field hoping to do the conversion from text to date. I tested it out as you suggested by adding the preceding 0's - and it worked - but because I'm lazy is there a way to "universally" add the preceding 0's rather than having to go through and add it to each record?

 

[BMeek]

There are some inherent problems to doing a "universal" update to the field when it is a text field. If you use a character count and add a leading zero to any field with 5 characters, that could incorrectly place a zero at the beginning of a date like 12558 (12/5/58) making it 01/25/58 so I would behesitant to use something without thinking it all the way through. Did the conversion from numeric to text keep the leading zero for the date? If so my example above would show as 120558 in your text field. Then adding a zero to any record with a length of 5 characters would work OK.

Test it before you change it universally. To change it universally, use an update query to add the zero to any record in the field with LEN[Birthdate] >6 characters.


Bryan Meek
bmeek@pacbell.net

http://www.handtech.com/meekent

A "Solution Provider" in your corner gives you the personal attention you need to find the right technology solutions for your business.