Solved

Convert text to date in MS Access query

Posted on 2014-02-20
6
2,897 Views
Last Modified: 2014-02-20
I get a file that contains date and time in a text field.  The system owners are unwilling to change the format of the output.   I need to convert it so it can be appended to a date field. I'm pretty good at parsing data, but this one is giving me trouble.  The idea is to write an Access query against the file and convert the "date" to a format that can be successfully appended into a date field in an Access table.  Here this the format:

Wed Jan 12 05:34:55 PST 2011

Thanks
0
Comment
Question by:upsfa
  • 2
  • 2
  • 2
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39874435
try this


cdate(mid("Wed Jan 12 05:34:55 PST 2011",5,6) &", " & Right("Wed Jan 12 05:34:55 PST 2011",4))
0
 
LVL 1

Author Comment

by:upsfa
ID: 39874454
Thanks, that gets date, but I am looking for date and time.  Also, not sure about how to address time zone.
0
 
LVL 39

Expert Comment

by:als315
ID: 39874455
If you need time also, you can use this function:
Function Txt2Date(Dt As Variant) As Variant
Dim A() As String, D As Date
Txt2Date = Null
If IsNull(Dt) Then Exit Function
A = Split(Dt, " ")
Txt2Date = DateValue(A(1) & " " & A(2) & " " & A(5)) + TimeValue(A(3))
End Function

Open in new window

In function you can also add time shift for time zone if you need
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 39874481
oh..

try this


cdate(mid("Wed Jan 12 05:34:55 PST 2011",5,6) &", " & Right("Wed Jan 12 05:34:55 PST 2011",4) + mid("Wed Jan 12 05:34:55 PST 2011",11,8))
0
 
LVL 39

Accepted Solution

by:
als315 earned 250 total points
ID: 39874496
Try this sample with Time zone converted to CST
DBdateconv.accdb
0
 
LVL 1

Author Closing Comment

by:upsfa
ID: 39874657
Thank you both.  The function by als315 is more comprehensive, but Rey's formula is what the user has decided to use.  Meets thier need and is easier to implement.

Thanks again.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

831 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