Urgent help required for a date conversion problem

[Notsogood]

I have received a Access97 database which has a date field filled with numbers.
The date of birth field is in the format : 19970131
And the date of birth field is a text field.
The software requires the date to be in dd/mm/yyyy order and also to be a date/time field.
When I try to change the text to date/time, Access deletes all dates of birth.
I am not sure how to solve this as I am very new to databases.
Can someone please help me soon?

 

[psalmon]

Try

DateValue(left([datetext],4)&"/"&mid([datetext],5,2)&"/"&right([datetext],2))

 

[Notsogood]

thanks, but where do I type this command. Can you please be a bit more specific? I have very very limited knowledge of databases.

 

[raskew]

Hi Neil -

One way of resolving this would be to:

1. Add a new date/time field to your table. Name it MyDOB.

2. Create a Select query that calls your text field ("txtDOB"?) and MyDob.

3. Turn it into a Update query. In the Update To: cell of MyDob, insert:

dateserial(Left(txtDOB, 4), mid(txtDOB, 5,2), mid(txtDOB, 7))

4. Run the query. After running it, turn it back into a Select query. You should see your text field and the new date field. At this point go back into Table design mode and specify the desired format for your new field.

5. Once satisfied that everything's working you can then delete the original text field.

HTH - Bob

 

[Notsogood]

Raskew, your method was perfect, just that Access could not do step 3. Data mismatch error!! So I used the update query given by psalmon & it worked.
Thanks a lot.

 

[idilbert6]

Thanks

This thread is more than a year old but is still helping people like me. Thanks!