[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Converting imported text expression to date (yyyymmdd to 24.06.1956)

Posted on 2007-10-21
14
Medium Priority
?
1,038 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:cisilay
  • 9
  • 5
14 Comments
 
LVL 75
ID: 20118637
Try:

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

mx

0
 
LVL 75
ID: 20118647
Now, where do you want to do this conversion?  In a query?  vba code?

mx
0
 
LVL 75
ID: 20118660
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:cisilay
ID: 20118665
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
 
LVL 75
ID: 20118677
See my last post above ...

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

mx
0
 

Author Comment

by:cisilay
ID: 20118711
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
 
LVL 75
ID: 20118746
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
 

Author Comment

by:cisilay
ID: 20118772
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 900 total points
ID: 20118848
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
 

Author Comment

by:cisilay
ID: 20119007
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
 
LVL 75
ID: 20120192
ok ... so, are we good ?

mx
0
 

Author Comment

by:cisilay
ID: 20120588
Yeah, you are good.
Thanks.
0
 
LVL 75
ID: 20120594
Don't forget to close the question and assign pts :-)

mx
0
 
LVL 75
ID: 20120782
I'm curious as to why the Grade of B ?

mx
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question