Convert Datetime to Double in MS Access
Posted on 2007-07-25
I need to write an update query in MSAccess that will set a column in one of the my tables equal to the value in a datetime column but converted to a double. Basically, i have two columns that are each a time, call them Time1 and Time2. I have a third colum, call it ElapsedTime, which basically equals (Time1 - Time2). Now, ElapsedTime ends up coming out formatted like 12:01:00 (that would be an elapsed time of one minute), but i need to take that value, convert it into a double, and put the returned value into a new field (call it ElapsedTimeDouble). I need to do this so i can sum the times, get averages, etc, blah blah blah, and then convert the numeric values back into the datetime format later once we're finished. Any idea how i would do this with an action query? Every Action Query i have tried so far has resulted in a type conversion failure, but what i'm aiming at is
UPDATE Table SET ElapsedTimeDouble=Cdbl(ElapsedTime)
Except, of course, that the query would actually work.