convert short text to date value

I am querying a linked table in MS Access and the field values are all text.  I want to do a calculation to determine the time difference between now() and opened_at.  The opened_at field has a value of 01-26-15 3:15 PM EST in text format.  How can i convert this to date so the calculation can be made?
szadrogaAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
you can convert the text field with

cdate(replace([opened_at]," EST",""))

to get the time difference, you will need a function in vba to return difference in days, hours, minutes, seconds

see this link

http://support.microsoft.com/en-us/kb/210604

look for  GetElapsedTime()
0
 
szadrogaAuthor Commented:
when I try to use cdate(opened_at) or cdate([opened_at]) i get an error message in the results. #Error
0
 
PatHartmanCommented:
Look again at Rey's suggestion. You have to get rid of the "EST" part of the string in order to have something Access will recognize as a valid date/time value.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Gustav BrockCIOCommented:
If you have other timezones than EST, a generic expression may be useful:

DateOpenedAt: CDate(Mid([opened_at],1,17))

To convert between timezones, a full collection of functions can be found here:
http://www.experts-exchange.com/Database/MS_Access/Q_27809329.html#a38237658

/gustav
0
 
szadrogaAuthor Commented:
my mistake, that was it.  thx for catching that
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
 
szadrogaAuthor Commented:
i meant to split the points assignment.  can this be done now?
0
 
Gustav BrockCIOCommented:
Yes. Call for attention.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.