Solved

Convert text to date in MS Access query

Posted on 2014-02-20
6
2,936 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 40

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 40

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

860 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