Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2012-03-28
11
Medium Priority
?
899 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 2000 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

609 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