Converting imported text expression to date (yyyymmdd to 24.06.1956)

Hi,
I have imported to the access 2007 some datas including some date fields but imported as a text format (yyyymmdd).
How can I convert this field(s) to date format (24.06.1956) ?
I'm a novice and if somebody help me, please indicate how can I enter some function in queries ?
Because I have already read some answers (maybe with the exact answers) but I really doesn't understood the explanation of the functions.
Thanks.
cisilayAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
ok ... here is the actual SQL to pasted into the query designer as I described above:

UPDATE Tracks
SET Tracks.[Peak Date]= Right([Peak Date],2) & "." & Mid([Peak Date],5,2) & "." & Left([Peak Date],4)

Are you doing this import on a one time basis or often ?

"And when I have to change the type of the field to date from text ?"

Well ... after you do the import AND conversion above, you may want to change Peak Date to a Date Time data type ... or, if this is something you are doing ofter, you may want to add a 2nd field - Date Time data type ... and do the conversion into that field, for example:

UPDATE Tracks
SET Tracks.[ConvtPeakDate]= Right([Peak Date],2) & "." & Mid([Peak Date],5,2) & "." & Left([Peak Date],4)

I have to leave for the afternoon ... try this and see how you do

mx

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try:

Right(YourImportedDate, 2) & "." & Mid(YourImportedDate,5,2) & "." & Left(YourImportedDate,4)

mx

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Now, where do you want to do this conversion?  In a query?  vba code?

mx
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
If in a query, you can create an Update query as such:

Open the query designer in Design View, click View>>SQL .... then enter this code:

UPDATE YourTableName
SET YourTableName.YourImportedDate= Right("YourImportedDate",2) & "." & Mid("YourImportedDate",5,2) & "." & Left("YourImportedDate",4)

where 'YourTableName' is the actual name of your table and 'YourTableName' is the actual name of your imported date field.

Then ... from the menu, Run the query.

** Make a BACKUP of your data first.

mx
0
 
cisilayAuthor Commented:
I think "In a query".
But can you help how can I do that also ?
Because I have no experience to do that.
Thanks
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
See my last post above ...

Again ... make a backup of you MDB before trying this ... just in case.

mx
0
 
cisilayAuthor Commented:
I think "YourTableName" is the name of the actual table and "YourImportedDate" is the name of the imported date field. You have mentioned for both the same name : YourTableName.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ok ... what is the name of your table ...and what is the name of the field in your table that contains the imported date field.

Also ... let me correct my original post now:


UPDATE YourTableName
SET YourTableName.YourImportedDate= Right([YourImportedDate],2) & "." & Mid([YourImportedDate],5,2) & "." & Left([YourImportedDate],4)

I replaced double quotes with brackets around the field name in the expression.

mx
0
 
cisilayAuthor Commented:
Table name : Tracks
Date Field : Peak Date

By the way, when I import from the text file, I describe this date field like a text field, otherwise it cannot import the datas.
And when I have to change the type of the field to date from text ?
0
 
cisilayAuthor Commented:
I'm doing this import on a one time basis, and with the first part of your answer, it's OK now. No need to convert to a new field.
Thanks for your help.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ok ... so, are we good ?

mx
0
 
cisilayAuthor Commented:
Yeah, you are good.
Thanks.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Don't forget to close the question and assign pts :-)

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I'm curious as to why the Grade of B ?

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.