Got the time?

Topic

New Reply

What do you have to do to add time?
I went to MSDN and they were very helpful should I need to calculate elapsed time with 2 different dates…I don’t.

I need to add a box of text boxes which contain Short time formatted values and just add them up. A 24 hour clock -AKA short time format- keeps rolling over.

Reply | Quote

Replies

Hmm, interesting question. As you said, the time “rolls-over” after each 24-hour period. However, this time is not lost, it has merely incremented the date portion of the field. You can use DateDiff to get the difference between your starting date and the ending date, and then use that to help create your elapsed time for display (reported in days and time).

Reply | Quote

Here’s a response I posted in another forum. The poster wanted to calculate time worked where the time started on one day and ended on another

Given any starting date/time:
thestart = #3/23/01 11:30:00 PM#

Reply | Quote

Unfortunately, there is no date data available. Therefore DateDiff doesn’t apply here.
It is a series of total time boxes (i.e. Start time- end time = time elapsed)
Now , picture a field of them (Time elapsed) which must be added up.
The date never changes but a detail report over a long period of time will ‘rollover’ when it hits 24:00
or 24 hours.
Where did I go wrong?

Reply | Quote

This is ugly but it can be done.

Say that our table includes an elapsed time field (short time format). Ignoring the other fields, we’d see something like this:

ElapsedTime
7:28
23:10

We want to sum these times and, from visual observation, it’s apparent the correct answer would be: 30:38.
(I’ve used only two records here, but the balance of this explanation will work with any number of records.)

If you used this query:

SELECT Sum(tblNurseTimes.ElapsedTimes) AS SumOfElapsedTimes
FROM tblNurseTimes;

Your return would be:

1.27638888888889

So what is that?

It’s one day (24 hours) and .2763

Reply | Quote

I did a separate query in SQL

SELECT Sum(Time) AS Total_Time
FROM qry_tblSafety;

‘ The total is
6.046527769

How do I get the hours and minutes?

‘The sample code from the MS site computes elapsed time, and it works if I provide two dates or at least two time values.
I just want to convert one value. However, the exercises were great and there was a link to another page that more exercises.

Actually, I went through all exercises
Created the table with the exact datatypes/entered the exact sample figures
…and still got an undeclared variable error on..

Function GetTimeCardTotal ()
Dim db As Database, rs As Recordset
Dim totalhours As Long, totalminutes As Long
Dim days As Long, hours As Long, minutes As Long
Dim interval As Variant, j As Integer

Set db = dbengine.workspaces(0).databases(0)
Set rs = db.OpenRecordset(“timecard”)
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![Daily hours]
rs.MoveNext
Wend
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

GetTimeCardTotal = totalhours &” hours and ” &minutes &” minutes”

End Function

Where’s the j as integer work?

Reply | Quote

I truly admire you for working through the exercises. That’s what we’re all supposed to do, but so few actually do it. I’m going to have to take a closer look at the problem, but may not be able to get back before Friday evening or Saturday morning.

Will get back as soon as I can.

Best wishes,

Bob

Reply | Quote

I’m baffled by your latest problem. Would venture a guess is that it’s your table. Reason being, I took the code directly from your post (not the referenced web site), changed it to employ tblNurseTimes | field: ElapsedTimes, and it worked perfectly–no errors.

The dim j as integer was most likely just something they’d used during development, and failed to remove. As you’ve noted, it doesn’t do anything. Try stepping through the code, line by line, to see if you can isolate the problem of the undeclared variable. Another possibility is that you picked up some hidden formatting characters when copying/pasting the code. Try copying/pasting the code directly from your post–it worked for me so it should work for you.

Bob

Reply | Quote

A quick search yielded the desired syntax.

CSing is CSng
I think heeding the advice of not pasting code directly from an HTML source scared me into
transcribing it…incorrectly.
Thanks for the help!!!
DOH!!!

Reply | Quote