upsfa
asked on
Convert text to date in MS Access query
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
Wed Jan 12 05:34:55 PST 2011
Thanks
ASKER
Thanks, that gets date, but I am looking for date and time. Also, not sure about how to address time zone.
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
In function you can also add time shift for time zone if you need
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks again.
cdate(mid("Wed Jan 12 05:34:55 PST 2011",5,6) &", " & Right("Wed Jan 12 05:34:55 PST 2011",4))