Bulk convert DDMMYYYY to YYYYMMDD?

[abm]

Hi

I have a table with a field where each record is of the format DDMMYYYY. There are a few thousand records. I would like to change the format to YYYYMMDD. Is there a way to make this change, across all of the records in one go, in Access 2010?

Thank you

[orange]

Access stores Dates as a numeric value.
For input etc Access uses American date representation MM/DD/YYYY

What exactly are you going to do with these dates?

[ranman256]

no change needed. The data is stored the same, its just how you look at it.
can you alter the table and reset the format of the field?

[abm]

Just to provide some additional information...

The field is entitled Date. Each record is formatted as 01012000 or 01052005. There are thousands of records.

If I go in to Design View, the date type is Text, and the field size is 255.

I thought of changing the data type to Date/Time, and specifying a custom format of ddmmyyyy. I assumed that one could save the database, and then change the custom format to yyyymmdd, following which Access would re-format the dates.

However, simply changing the data type to Date/Time results in an error message, when saving the table, of "Microsoft Access encountered errors while converting the data. The contents of fields in [all of the] record(s) were deleted. Do you want to proceed anyway?".

[orange]

If you have text that represents a Date, and you want to change the data, you can do so with the Mid() , Left() and Right() functions.

Format works with strings. It is for presentation.
Dates are stored as numbers.

It seems you want to change your data values.
ddmmyyyy to yyyymmdd but it isn't yet clear as to what you intend to do with this data.
In Access terms, this is NOT formatting. This is an update/change to the data.

To get a Date you could do a DateSerial(Right(YourDate,4),Mid(yourDate,3,2),Lef t(yourDate(2))

To restructure your text you could do
Right(yourDate, 4) & Mid(yourDate, 3, 2) & Left(yourDate, 2)

What you should do depends on what you are planning to do with the "reformatted dates".

[abm]

I am using Access purely to store data which I intend to export as a CSV file.

[orange]

So, what is the purpose of the reformatting? Does the csv file need the field restructured?
If it isn't a Date for use by Access, you can leave it as text.
If you need to restructure the data with Access, you can manipulate the text as I showed.

eg
To restructure your text you could do
Right(yourDate, 4) & Mid(yourDate, 3, 2) & Left(yourDate, 2)

[abm]

Yes, that's right. The system to which the CSV file will be imported requires the dates to be in the format YYYYMMDD. With the code you have provided, how would I apply that to all the records in a field, and where would I type the code?

[orange]

Your question indicates this is all new to you.
Here is a link related to several items related to text files and Access.

Good luck.

[June7]

To fix your field in Access, create a new field as date/time type and run an UPDATE action to populate:

UPDATE tablename SET [new field] = CDate(Format([old field], "00/00/0000"))

This assumes every record has a value in [old field]. If not:

UPDATE tablename SET [new field] = CDate(Format([old field], "00/00/0000")) WHERE NOT [old field] Is Null

Then your export can be a query that formats the date for the CSV.

Format([new field], "yyyymmdd")

Now maybe delete [old field] and rename [new field] to [old field].

[abm]

Thank you for the suggestion. Every record does have a value.

If I try to implement your suggestion as a query in SQL View, although Access initially says "you are about to update xxxxxx rows", when I click "Yes" to proceed, it then says "Microsoft Access didn't update xxxxxx fields due to a type conversion failure".

Both the old and new fields are of the Data Type 'Date/Time'. Is there something else that I must do?

[June7]

So the conversion of old field from text to date/time was successful? I would expect conversion of 01012015 to fail. I thought you said it did fail? I am confused.

If the old field is date/time then why try to populate a new field?

[abm]

Actually, problem solved, as I've realised that you can change the way Access structures dates when exporting data by clicking 'Advanced' in the Export Text Wizard and changing a few settings. Apologies for any confusion caused.

[orange]

Glad you have it resolved.