[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Convert Datetime to Double in MS Access

Posted on 2007-07-25
30
Medium Priority
?
2,228 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:zhenchyld
  • 7
  • 6
  • 6
  • +4
30 Comments
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 400 total points
ID: 19566464
Hello zhenchyld,

You cannot use an UPDATE query to change the data type of a field.

Out of curiosity, why do you think you need to do this?  It is almost certainly unnecessary...

Regards,

Patrick
0
 
LVL 32

Assisted Solution

by:jadedata
jadedata earned 400 total points
ID: 19566473
Try tinkering with this....

Public Function Duration(vTimeIn, vTimeOut, vOutputFormat)

  Dim vDur
 
  On Error GoTo Duration_Err:
 
  vDur = DateDiff("s", vTimeIn, vTimeOut)
 
  Duration = Null
 
  Select Case vOutputFormat
  Case "HMS"
    Duration = Int(vDur / 3600) & ":" & Right("00" & CVar(Int(Int(vDur / 3600) / 60)), 2) & ":" & Right("00" & CVar(vDur Mod 60), 2)
  Case "HM"
    Duration = Int(vDur / 3600) & ":" & Right("00" & CVar(Int(Int(vDur / 3600) / 60)), 2)
  Case "H"
    Duration = Format(vDur / 3600, "#,##0.0")
  Case "M"
    Duration = Int(vDur / 60) & ":" & (vDur Mod 60)
  Case "S"
    Duration = vDur
  End Select
 
Duration_Exit:
 
Duration_Err:
  Select Case Err
  Case Else
    ErrMsg "bas_HandleDates.Duration", error$, Err, False
    Resume Duration_Exit:
    Resume
  End Select
End Function
0
 
LVL 32

Expert Comment

by:jadedata
ID: 19566490
you will need to swap out my error trapping routine and make a few adjustments...but this might be helpful to you in your search for a duration of elapsed time
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 800 total points
ID: 19566521
this should work, if you set the ElapsedTimeDouble field to Number/Double

UPDATE TableName SET ElapsedTimeDouble=Cdbl([ElapsedTime])
0
 
LVL 75
ID: 19566527
Cdbl(#12:01:00#) =0.500694444444444

of course ... because the Date Time data type IS a double precision value :-)

mx
0
 
LVL 75
ID: 19566542
You might try:

CDbl(Cdate(#12:01:00#))

mx
0
 

Author Comment

by:zhenchyld
ID: 19567943
matthewspatrick -  im not trying to change the datatype.  im trying to update a new field with data based on pre-existing data.  next time read the question before you respond.

jadedata - It's a good function but i still have to pass the value for each record back into the table, so i would have to loop through every record in the table, pass the variables into the function, then pass the returned value back into the table, which sounds like a lot of ADO and too much work...

Basically all i need is a field in the table that has the elapsed time, then i need to be able to sum and display those elapsed time values in a crosstab query.  that's all.  say, i have 4 fields in my table, User, TimeIn, TimeOut, and ElapsedTime.  The User John Doe has ten entries with the time he clocked in and the time he clocked out.  i need to be able to tell the difference in those times and then sum the differences for all of the ten records (this is not exactly the data im working with but it is a much easier paradigm to explain) to be able to cut John Doe his weekly paycheck.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 19567985
i run that function in update and select queries alike and iterative code without much trouble...
0
 
LVL 75
ID: 19567996
"next time read the question before you respond."

Ahhh ... I would go EASY on that sort of mannerism .... just an fyi !!!  Else, no one will be  responding!!!

mx

0
 

Author Comment

by:zhenchyld
ID: 19568034
capricorn you're an f'in genius.  either that or i'm waaaaaaay off my game today; probably both.

after i do the manipulations i'll need to reformat teh double values back into datetimes but i think i can manage with the logic in jadedata's function.

thanks all.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19568035
zhenchyld said:
>>next time read the question before you respond

I misread the question.  It happens.  Getting snippy about it is not a terrific long-term
strategy.

That said, based on your description, I still maintain that this is unnecessary.  All it is doing
is creating redundant data.
0
 
LVL 13

Expert Comment

by:Ryan
ID: 19568070
If you just do the math, date1-date2 and update it in your other table...and the field in that table is formatted as a double it should be all you need. No conversion function in your SQL required.
0
 

Author Comment

by:zhenchyld
ID: 19568364
matthewspatrick - this is not a permanent table with any bound forms.  it is a temporary table im using to do some reports for Q's 1 and 2 for some useless and incompetant corporate monkeys that i have a serious distaste for and have habits of asking for useless and difficult to get data for no reason other than that they can and it keeps me from doing anything productive.  Sorry for being an ass but im under a LOT of stress and just put in my two weeks notice because of BS like this.

anyway i prefer to convert my dates to doubles before manipulating them just because it makes the calculations a lot easier (in my head, at least).
0
 
LVL 58

Expert Comment

by:harfang
ID: 19568390
santé, et meilleures féliciations!
(°v°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 19568473
ouch! wrong thread. sorry about that!

Since I'm here anyway...

You don't need an update query. Anything you can do with a double field, you can do with a date/time field or expression. To take your timesheet paradigm, you can very well write things like:

SELECT PID, SUM(TimeOut-TimeIn)
FROM TimeSheet
GROUP BY PID

You should understand that there is no "conversion" between double and date. It's the same data type, with different default formatting, that's all.

Out of curiosity, what was the difference between your own query and cap's?

(°v°)
0
 
LVL 75
ID: 19568519
"You should understand that there is no "conversion" between double and date. It's the same data type,"

As I mentioned :-)

Marcus ...  I love it what you talk as above :-)

mx
0
 

Author Comment

by:zhenchyld
ID: 19568822
harfang -

...

the only difference was the ' [    ] ' brackets.  without them it doesn't work.

all that work because i left out some brackets.  oh, the shame.

0
 
LVL 58

Expert Comment

by:harfang
ID: 19568857
Hmm, no. The square brackets are required only when your field contains spaces and other reserved characters, or if their name can be confused with a reserved word.

You don't need the square brackets in your example.

(°v°)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19568964
i want to see how it can be done without including the name of the table
in the  cdbl(elapsedtime)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19568993
oh yeah,you can do it,

 edit the sql after you created the update query using the QBE
0
 
LVL 58

Assisted Solution

by:harfang
harfang earned 200 total points
ID: 19569135
Sorry, cap, I was in a jesty mood.

I guess most of us experts know that the real reason your query worked and not that in the question is because "table" is a reserved word.

UPDATE TABLE ....  will always throw an error
UPDATE TableName ... your version: not reserved
UPDATE [TABLE] ... also allowed (but not recommended)

Cheers and sorry again for the pique.
(°v°)
0
 
LVL 13

Assisted Solution

by:Ryan
Ryan earned 200 total points
ID: 19569185
It would be highly recommended to use proper naming conventions. The method I used it to put tbl in front of every table name, and qry in fron of every query, that why when I'm looking at a list of both, I can easily differentiate, and if i choose to call a table "table", then "tblTable" works.
0
 

Author Comment

by:zhenchyld
ID: 19569261
harfang -  you're right it doesnt require the brackets.  I dont know what the crap i was doing before to get it to not work.  also, fyi, the table wasn't actually called 'table'.  'tblTable' looks pretty goofy.

Many thanks all around.
0
 
LVL 58

Expert Comment

by:harfang
ID: 19569349
I was half expecting that, of course. BTW, what about qryTable or tblQuery? -- (^v°)
0
 
LVL 13

Expert Comment

by:Ryan
ID: 19569375
I wasn't saying I did that. Just that you could and won't violate reserved words. I like names like tbl1,tblA, qryItemsInTbl1ButNotTblAorqry3.  Real descriptive so if I ever get fired, the next sucker will know whats going on.
0
 
LVL 58

Expert Comment

by:harfang
ID: 19569435
I absolutely agree, Mr Bullwinkle, and I use a similar naming convention. I was really still in a jesty mood.
(°v°)
0
 
LVL 75
ID: 19569469
"jesty " ??  must be a Geneva term :-)

mx
0
 

Author Comment

by:zhenchyld
ID: 19570078
*still trying to figure out what that thing is after all of harfangs posts*

after close scrutiny, i think it's an owl face.
0
 
LVL 75
ID: 19570915
It's his special naming convention for himself :-)

mx
0
 
LVL 58

Expert Comment

by:harfang
ID: 19571576
LOL. Yes, it's an owl, and my profile has an expanded version (harfang is the name of the snow owl). See http://www.google.com/search?q=harfang
Wow! I'm a bit flattered to see that my EE profile is actually on the first google page for that keyword...
(°v°)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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 …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

831 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