update query to convert text field mmddyy to mm/dd/yyyy (1 Viewer)

[trythis]

Cannot find the answer after much reading. I am not sure how to apply the answers I have found. Thank You in advance.

I have a switch that produces a .txt file that I import into Access.

Access imports the date as a text field mmddyy. If I try to change it to number or date on import it deletes some fields.

So a date might appear as 070809 it always keeps the 0's

How can I convert 070809 or mmddyy to 07/08/2009 mm/dd/yyyy I don't really care how it looks as long as Access recognizes it as a date.

I am in the US and I just read about the cdate function limitations.

Thank You ,
Tina

 

[Brianwarnock]

Use Dateserial along with left , mid and Right as relevent.
Brian

 

[giovaanni]

CDate(LEFT(string,2) & "/" & MID(string,3,2) & "/" & Right(string,2))

 

[Brianwarnock]

This
CDate(LEFT(string,2) & "/" & MID(string,3,2) & "/" & Right(string,2))
I think would convert 070809 to 07/08/09 the poster specifically asked for 2009
Dateserial produces a date in your default format.

Brian

 

[giovaanni]

Cdate does precisely that. Try it.

 

[Brianwarnock]

Cdate does precisely that. Try it.

I just did and was coming back to apologies, I never used Cdate prefering dateserial, its less typing.

Dateserial(Right(string,2),mid(string,3,2), left(string,2))

Brian

 

[trythis]

Sorry I am new. Do I put this in the criteria of an update query.

What I have been doing is changing to the SQL view of an update query and adding there.

That did not work so where do I put this?

Dateserial(Right(string,2),mid(string,3,2), left(string,2))

Thanks.

 

[giovaanni]

If you're trying to run an update on a text field to convert your data to date/time you can't do that. You are not allowed to change a field's datatype with an update query. What are you trying to use this information for? What you can do is create an expression field in an existing query that would effectively translate this string to a datetime field in your query but it wouldn't exist without the query. If this would work then just put that in your select query in a new column and make sure it is referencing the field that your string is in.

If your intention was to update the string with the proper date format but still have it remain a string, you would have to do something different.

I would first do a maketable query with your table's key field and an expression field with the value: CSTR(Dateserial(Right(string,2),mid(string,3,2), left(string,2))). This would give you the key field and the proper formatted text you are after for the date field. Then you could query this to update the original table.