Link to home
Start Free TrialLog in
Avatar of upsfa
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
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

try this


cdate(mid("Wed Jan 12 05:34:55 PST 2011",5,6) &", " & Right("Wed Jan 12 05:34:55 PST 2011",4))
Avatar of upsfa
upsfa

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

Open in new window

In function you can also add time shift for time zone if you need
SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of upsfa

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.