Solved

Access function or formula

Posted on 2011-09-11
5
235 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
Comment Utility
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
Comment Utility
You could also use Format:


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

/gustav
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
:-)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
gustav,

Very elegant!

Patrick
0
 
LVL 49

Expert Comment

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

/gustav
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
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.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

744 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