Converting text to dates

[GLS]

Hi

I have inherited an Access 97 database which has the dates
in text format eg '20040425'. Changing the data type in
design view deletes all the records. Anyone know how I
can convert these dates from text to date without data
loss.

Thanks

GLS

 

[Cheryl Fischer]

You can add a new field (of Date type) to your table and then do an Update
query to fill it with the date value from your text field. Here is the SQL
for an Update query using the DateValue() function to convert the text to a
true date:

UPDATE [MyTable] SET [MyTable].NewDateField = DateValue(Left([TextField],4)
& "/" & Mid([TextField],5,2) & "/" & Right([TextField],2))


hth,

 

[GLS]

Thanks Cheryl - that worked great.

Much appreciated

GLS

-----Original Message-----
You can add a new field (of Date type) to your table and then do an Update
query to fill it with the date value from your text field. Here is the SQL
for an Update query using the DateValue() function to convert the text to a
true date:

UPDATE [MyTable] SET [MyTable].NewDateField = DateValue (Left([TextField],4)
& "/" & Mid([TextField],5,2) & "/" & Right([TextField],2))


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Hi

I have inherited an Access 97 database which has the dates
in text format eg '20040425'. Changing the data type in
design view deletes all the records. Anyone know how I
can convert these dates from text to date without data
loss.

Thanks

GLS

.