Results 1 to 9 of 9

Thread: Got the time?

  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Got the time?

    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.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Got the time?

    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).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Jun 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Got the time?

    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#

  4. #4
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Got the time?

    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? <img src=/S/help.gif border=0 alt=help width=23 height=15>

  5. #5
    New Lounger
    Join Date
    Jun 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Got the time?

    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:
    <pre>ElapsedTime
    7:28
    23:10
    </pre>


    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

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Got the time?

    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<font color=blue> undeclared variable</font color=blue> 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<font color=red>(CSng</font color=red>(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?

  7. #7
    New Lounger
    Join Date
    Jun 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Got the time?

    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

  8. #8
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Got the time?

    A quick search yielded the desired syntax.

    <font color=red>CSing</font color=red> is <font color=blue>CSng</font color=blue>
    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!!!

  9. #9
    New Lounger
    Join Date
    Jun 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Got the time?

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •