Solved

Convert Text field  yyyymmdd in Access to Date format mm/dd/yyyy

Posted on 2012-03-28
11
886 Views
Last Modified: 2012-03-31
I am importing about 3000 records from an Excel file into an Access table which will eventually end up in a sql table.  There are two columns (Text) in the Excel file that have a date recorded as yyyymmdd (example 20091031 for Oct. 10th 2009).  In the end I would like to have the field in Access be a date type and show up as 10/31/2009.
0
Comment
Question by:MarkSnark1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
11 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 500 total points
ID: 37778544
You could use this:

SELECT Mid(yourField,5,2) & "/" & Mid(YourField,7) & "/" & Left(YourField,4)
Etc...
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 500 total points
ID: 37778556
To explicitly convert that to date:

CDate(Mid(yourField,5,2) & "/" & Mid(YourField,7) & "/" & Left(YourField,4))
0
 

Accepted Solution

by:
MarkSnark1 earned 0 total points
ID: 37778561
I Ended up doing this and then imported it. Thank you.

In Excel:
 
Select one column with "dates" of the form yyyymmdd.
 
Activate the Data tab of the ribbon.
 
Click Text to Columns.
 
Select Delimited, then click Next >.
 
It doesn't matter which delimiter you choose, it won't be used here. Click Next >.
 
Select Date as column data format, and select YMD from the date format dropdown.
 
Click Finish.
 
Repeat the above steps for the other "date" column.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:MarkSnark1
ID: 37778591
I've requested that this question be closed as follows:

Accepted answer: 0 points for MarkSnark1's comment #37778561

for the following reason:

I found the solution in Excel and then imported into access.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37778592
I object to this closure - both solutions will work.

My comments were posted with no knowledge of what had been tried outside of this thread, but they are tested - and will format the data as described in the original post.
0
 

Author Comment

by:MarkSnark1
ID: 37778620
I did not try the first solution so I do not know but I am certainly not adverse to awarding the points to Mbizup.  I was just uncertain how to go about this.
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 37790732
These are going out as "neglected questions" when solutions have already been accepted.
0
 

Author Comment

by:MarkSnark1
ID: 37790757
Sorry about the confusion and thank you to MbiZup for his work and help.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 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