Change Date Format on Data Table

[scottmfitzge]

Hi Guys... I need some help pls. I have a very large data table, that contains a date field. However the date has been stored in a particular format: 20090602, and the field is highlighted as a Number in the field type properties. I want to change the field type to Date and change the format to 02-06-2009 - any ideas how i can do this??

Thanks for your help...

 

[Galaxiom]

Add a Date formatted field to the table.
If DateText is your original field.
Run an update on the new field using the expression:

DateSerial(Left(DateText,4), Mid(DateText,5,2), Right(DateText,2))

Then delete the original field and rename the new one to the original field name.
Do not rename the DateText field or it will wreak havoc as Access updates it everywhere.

 

[scottmfitzge]

Sorry.. I am really new to this, how do i run an update on the new field. I have created it, but need to run the update. Also i am going to be adding large amount of data at the beginning of each month, do i have to run this update each time, or can i have it so that it updates when i import the data?

Thanks for your help

 

[boblarson]

You should import to an interim table and then in an append query moving the data to the real table you would do the conversion at the same time in the append query.

 

[scottmfitzge]

Hi - Really sorry but how would I go about creating the append query? I am pretty new to Access so would really appreciate some steps that I could follow to do this. I also don't know how to run a field update as advised by 'GalaxiomAtHome'?Cheers, Scott

 

[Galaxiom]

how would I go about creating the append query?

Query type can be changed by right clicking on the background of the table area, selecting Change To and then choosing the type of query.

An Update query will add another row to the field design grid called "Update To". Add the field name you want to update (ie the new date formatted field) and put the conversion expression into the Update To box. Then save and run the query.

Since you are new to this, backup the table before you run the query in case of mishap.

An Append query will ask you which table you want to append the records to. It will also add a design row called "Append To". Create the records you want to add just like in a Select query and add the field names from the destination table in the Append To box. Save and run the query and the records will be added to the desination table using these fields.

What Bob is saying is to import the new data (which presumably has the text date format) into a separate table then convert it during the append query by using the expression in the Field box that you are appending to the new date style field.

 

[neileg]

If you are constantly updating the data and you need to change the format on the new data as well as the old, I wouldn't store the reformatted date. If you use the same expression in a select query, simply use the query anytime you would use the table. This way you never need to run an update query.