?
Solved

Convert text to date in MS Access query

Posted on 2014-02-20
6
Medium Priority
?
3,715 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 41

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
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.

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 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 41

Accepted Solution

by:
als315 earned 1000 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

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

A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

593 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