[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Access function or formula

Posted on 2011-09-11
5
Medium Priority
?
254 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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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 53

Expert Comment

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


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

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

Expert Comment

by:Patrick Matthews
ID: 36519777
gustav,

Very elegant!

Patrick
0
 
LVL 53

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

591 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