Solved

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

Posted on 2012-03-28
11
853 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
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
Comment Utility
These are going out as "neglected questions" when solutions have already been accepted.
0
 

Author Comment

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 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 …

762 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

10 Experts available now in Live!

Get 1:1 Help Now