Solved

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

Posted on 2012-03-28
11
875 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

810 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