Change numbers to Date Format and export in delimited

[AnderSteve]

I have a large database that needs to have the date field changed in a Table from (example: 122270 and changed to read 12/22/70 or 12/22/1970) and EXPORTED with the slash marks in the database.

How can this be done?

 

[traingamer]

Assuming that all of your fields are 6 characters (AND all dates are between 1930 and 2029).
one way:
use an update query to replace the existing data

UPDATE yourtbl SET yourtbl.yrfld = Left([yrfld],2) & "/" & Mid([yrfld],3,2) & "/" & Mid([yrfld],5,2);

then change your field type on the table to date

traingamer

 

[dhookom]

You state "have a date field changed". Are you sure it is a date/time field or is it text? If it is test I would be concerned about the conversion from text to a date and possibly date/time format conflicts.

I think a more robust expression would be something like:
DateSerial(2000+Val(Right([YourField],2)), Val(Left([YourField],2)),Val(Mid([YourField],3,2)))

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SkipVought]

I second Duane!!! Consider REAL DATES!

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]