Dates / DateSerial

I imported a .txt file into Access 2000.

One file has a Date of Birth (DOB) field. In the .txt file it is a text field with the structure: yyyymmdd

If I change the data field type to Date/Time when I import (through the specifications), I generate errors for every record.

If I leave the data field as text, import the file and then attempt to change the field type in the table structure, I get the same set of errors.

One person suggested using the DateSerial in a query and create a new field which is of the type date and breaks the date into its component parts separated with a - (2001-01-01). I used the code sent and indeed it did split the yyyymmdd into yyyy-mm-dd (format is great), only problem is that it changed the dates. . . 20010101 became 2008-10-18. My guess is that I am using the DateSerial function incorrectly in the query (well, that's pretty obvious).

Any suggestions? Or, can anyone tell me how to use the DateSerial function properly so that it translates yyyymmdd to yyyy-mm-dd and changes the field type from text to date?

Thanks, KC

And now I can sleep tonight!

Thanks so much for your reply . . .take care, kc

to change them to yyyy-mm-dd format from yyyymmdd you can do the following

assuming you have a table named Table1 and the text column named date2

SELECT Left([table1]![date2],4) & "-" & Left(Right([table1]![date2],4),2) & "-" & Right([table1]![date2],2) AS Expr1, table1.date2
FROM table1;

this is just manipulating the string takeing the left 4 characters (the year) and adding a - character then takinge the right 4 characters and taking the left 2 characters (the month) adding a - and then the right 2 charactars (the day)

its now just a matter of making this an update SQL statement instead of a select

HTH
JS