Solved

Access function or formula

Posted on 2011-09-11
5
237 Views
Last Modified: 2012-05-12
I get a text file from a linked SQL table that contains 201101021515  which is YYYYMMDDHHMM or "01/02/2011 03:15 PM"  Is there a formula / function to
convert 201101021515 to "01/02/2011 03:15 PM"

My goal is to export to Excel from Access, so i would like Excel to see the "01/02/2011 03:15 PM" as a date/time value that I can perform time calculations with.
0
Comment
Question by:dastaub
  • 2
  • 2
5 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36519587
If that value is held as text:

CDate(Left(MyColumn, 4) & "-" & Mid(MyColumn, 5, 2) & "-" & Mid(MyColumn, 7, 2) & " " & Mid(MyColumn, 9, 2) & ":" & Right(MyColumn, 2))
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36519699
You could also use Format:


CDate(Format([YourFieldName], "@@@@/@@/@@ @@:@@"))

/gustav
0
 
LVL 75
ID: 36519733
:-)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36519777
gustav,

Very elegant!

Patrick
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36521033
Yes, I like it too, though not invented by me.
A good reminder of the power of Format.

/gustav
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

911 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

22 Experts available now in Live!

Get 1:1 Help Now