Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Totalling time (WINXP - ACC 97)

    Table1:
    FieldName = fStart Data type Short Time
    FieldName = fEnd Data type Short Time
    FieldName = fTotal Data type Short Time

    I placed the 3 fields on a simple form (using the same format as in the table).
    The code on fEnd_AfterUpdate is :
    TOTAL = Me.fEND - Me.fSTART
    Works fine. Gives me the elapsed time in hours and minutes.

    The problem:
    I tried to run a query to sum the totals and it is not giving the expected results.
    I have 3 records whose totals are 3,6 11 - The sum of the times should be 20.
    The result is: 0.833333333333

    I believe I need to adjust a format somewhere, but cannot figure out where!

    SQL for query:
    SELECT Sum(test2.TOTAL) AS SumOfTOTAL
    FROM test2;

    Thank you for help.

    Michael Abrams

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Totalling time (WINXP - ACC 97)

    Access stores dates and times in units of days. Times are fractions of 1 day. 20 hours = 20/24 day = 0.833333 day, so the result is OK. You can do two things:

    - If the total is always below 24 hours, format the result as h:mm
    - If the total can be over 24 hours, multiply the result by 24 to get (decimal) hours.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totalling time (WINXP - ACC 97)

    Michael,

    In you query got to properties and change the format to short time.

    Don
    Attached Images Attached Images
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totalling time (WINXP - ACC 97)

    Don - I didn't realize that I could type in my own format. Short time was not offered !
    It works perfectly.

    Hans - It will always be less than 24 hours, so I am also going to try your suggestion.

    Thank you both very much.

    Michael

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totalling time (WINXP - ACC 97)

    Michael,

    It seems that Access only displays the formats that it thinks it should display. If you remove the group by and then look at the format you will see 'Short Time' in the drop down along with other date related formats (this is provided that your field was declared as Date in the table).

    Have a nice day. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Don <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

Posting Permissions

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