Results 1 to 5 of 5
  1. #1
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185

    Format For Minutes:Seconds - Sum Not Working

    Hello,

    I have a field in a table that holds time data in minutes and seconds. No record will ever be on its own over 60 minutes. In fact there will more than like never be a record over 10 minutes but that is beside the point.

    I have tried setting the data type as date/time with a format of n:ss and input mask of 00:00 but this did not work.
    I have tried setting the data type as number with a field size as double, formatted as 00\:00 with an input mask as 00:00 but this did not work.

    I have been testing in the table by summing the total of the field and the result I am trying to achieve is not happening.
    AS AN EXAMPLE:

    I have 2 records
    Record 1 is 05:59
    Record 2 is 04:02

    The sum I would like to see is 10:01
    The sum I am getting is 09:61.

    What do I need to do to get the sum to be 10:01?

    As a side note, I do not want to ever see hours. If the sum goes over 60 minutes I would only like to see something like 65:59. What I am trying to accomplish will rarely go over an hour but may occasionally see minutes in the low 70's. Never will it be over 79.

    I have gone over some of the other posts here that I thought would help me figure this out before posting a new thread on what I am sure is an ooooollld topic that has been solved a million times before but was obviously unsuccessful in that attempt.



    I am using Access 2016.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    TimeElapsed-v1.zip
    Take a look at this; it adds up 3 elapsed times, but you can get the drift...

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, since I worked on it I'll paste what I've now got on my clipboard. AFAIK, you'd need an integer field and a calculation that you perform on a form or query. If TimeSum contained 359 (5 min. 59 sec. in your example) then this would return 06:59. I see that the value is rounding up, which is no good, but since you already have an answer I'll leave it at that for now.

    Code:
    SELECT tblTimeSums.TimeSum, Format([TimeSum]/60,"00") & ":" & [TimeSum] Mod 60 AS TimeCalc, Format([timesum]/60,"00") AS expr1
    FROM tblTimeSums;
    EDIT
    Changed my mind. So this works if you don't need leading zeros in the minutes portion. Otherwise, wrap the Int function in the Format function I guess
    Int([TimeSum]/60) & ":" & Format([TimeSum] Mod 60,"00")
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185
    davegri,

    I can make that work! Thanks for the example!!

    Micron,

    Thank you for your input as well. I can also use that method!!

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    should mention that if the time integer value is null, you'd probably get ":" as a resulting value unless you alter the expression to deal with the possibility.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 03-09-2017, 12:14 PM
  2. Minutes and Seconds format for record in Database
    By vegetamaker in forum Access
    Replies: 2
    Last Post: 09-03-2016, 06:31 AM
  3. Replies: 4
    Last Post: 03-19-2015, 08:43 AM
  4. Replies: 3
    Last Post: 05-13-2014, 01:55 PM
  5. Convert seconds to HH:MM:SS format
    By sai_rlaf in forum Access
    Replies: 2
    Last Post: 01-19-2012, 12:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums