Solved

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

Posted on 2012-03-28
11
865 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
  • 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
 

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

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.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now