Update Query for Date

[Nabil Bugaighis]

Hi. I would appreciate help an Update Query for Microsoft Access 2003 to change the date format for some entries.

Some of the dates are written to the table in this format:20130410 112143 , which is YYYYMMDDHHMMSS .
The column name is PickupDate (data type=Text) , and the Table name is Tracking

I need to change only the entries in this format to a YYYY-MM-DD format.
Nothing else in the table should change.

So first filter for dates with 14 Characters (or any other way to filter for YYYYMMDDHHMMSS) , drop the right 6 characters, and then re-format the date.
This would be my novice approach to this, but all that matters to me is the result.

Thank you in advance.

[TheSmileyCoder]

Start by writing a select query in the query designer where you successfully select all those rows with len([PickupDate])=14.

Then switch the select query to a update query, and use the following DateSerial function to update the information:

dateserial(left([PickupDate],4),mid([PickupDate],5,2),mid([PickupDate],7,2))

[Nabil Bugaighis]

Hi, Thank you so much.
Is there a way to make it in one query as I need to run this daily.
I was hoping to have a shortcut to the query I can just run each day to fix the dates in the Tracking Table.

[TheSmileyCoder]

It is only 1 query. I just instructed you to start of with a selection query as a test to make sure you have selected the right records. Once the selection is properly in place, you can then switch the type of query from select to update.

[Nabil Bugaighis]

Excuse me for being thick :) I got it and it works like a charm. Much obliged.