Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    Tampa, FL
    Posts
    1

    Post Unanswered: calculate over 24 hours

    I am trying to calculate times (hh:mm) and have viewed:

    http://dbforums.com/archives/t315587.html

    That archive post says I am doing it wrong because, yes, I have been using date/time. I do not understand what I need to do to change this (maybe step by step). I know how to change the filed to a text field, but my questions is, how do I change the text field format, etc so that I get a hh:mm format without seeing the "AM" or "PM" and get it to calculate over 24 hours?

    Additionally, I have an SQL statement to total up the times as well. It currently shows total minutes (721, etc).

    SELECT [Itemized comp time].[Teacher Name], Sum([Itemized comp time].[Time Earned]*24*60) AS [Time Earned], Sum([Itemized comp time].[Time Used]*24*60) AS [Time Used], Sum([Itemized comp time].[Time Earned]*24*60)-Sum([Itemized comp time].[Time Used]*24*60) AS [Current Balance]
    FROM [Itemized comp time]
    GROUP BY [Itemized comp time].[Teacher Name];

    How can I format that to show a total in hh:mm format?

    I am fairly new to MS Access but am willing to learn. I have no VB experience and very little with macros so you will have to speak in kindergarten for me to understand at first. Thanks.

  2. #2
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    You getting into one of my biggest problem in the past. Anyway I will try to explain it simple.

    First of all, if I understand, you want to sum different times and want to display it in hh.mm. Let's say you have 42 minutes and 35 minutes and you want as a result 1 hour 17 minutes?

    Right, if so...

    If you are running it from a query you could use this:

    SELECT (Int(Sum(TimeField)/60) & " Hours" & sum(TimeField) Mod 60 & " Minutes") AS [Total Time] FROM tblMyTable WHERE TaskID=1

    If you want to do it in a form

    Me!txtTotalTime=DLookUp("Int(Sum([TimeField])/60)", "tblMyTable1", "[TaskID]=1") & "Hours" & DlookUp("Sum([TimeField]) mod 60", "tblMyTable1", "[TaskID] = 1") & " minutes"

    Hope this helps
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ain't nothing wrong with using datetime fields. I use them all the time to represent spans, and I find them very convenient.

    I assume [Time Earned] and [Time Used] are datetime fields?

    If you leave the aggregate values in datetime format by ommitting the *24*60 bit then you can display the resulting values any way you want. My preference would be dd:hh:mm, like this:

    Format((sum[Time Earned]), "dd:hh:mm")

    ...but if you insist on hh:mm where hh could be greater than 24, you can do your arithmetic on the final value just as easy (or easier) than on all the input values.

    I completely disagree with the post you cited where it was recommended that a string be used to hold duration rather than the a datetime field. Hogwash. If you do this, all you will get for your trouble is plenty of experience converting text values back into time values so you can calculate sums, differences etc. If you intend to do calculations on the field, you should at least keep it numeric!

    blindman

Posting Permissions

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