Access Report - Sum a Time Field in an Access Report

[terpdavid]

I have a [Start time] and [Finish Time] field in my access 2007 db.
I've also got a [Total Hours] field for each record that calculates just fine as: =Format([Start Time]-1-[Finish Time],"Short Time") ... i got this off of this forum (great job).

My problem is in trying to get the sum of [Total Hours] to calculate automatically in my [Hours to Date] field; in the right format. I'm using: =Sum(DateDiff("h",[start time],[finish time])). I've also tried to use =SUM(Total Hours), but I get a whole number of "298," without a xxx.xx decimal to include the totaled minutes, or an hh:mm format I'd take either...whichever is easier). Any help with this?

 

[dhookom]

Date/Times are numbers with the integer part being the number of days since 12/30/1899 and the fraction part being the time where .25 = 6 hours or 6:00 AM.

You can subtract one date value from another and the result will be expressed in days. To get the number of hours rather than days, multiply by 24 since there are 24 hours in a day.

When you format a date/time, it often renders it useless in other date/time calculations since it becomes a text string.



Duane
Hook'D on Access
MS Access MVP

 

[terpdavid]

Thanks dhookom,

I had read some of your posts and was hoping you'd respond to this one.

I'm obviously a novice using this, and need a little more clarification.

The field i'm trying to SUM is already in hours:minutes format. I'm not subtracting "days," but hours (start Time - Finish time). I get that subtraction calculation result in my (Total Time) field. It works well. It's when I try to SUM the Total time field, with the result populating to my (Total Time To-Date) field that I loose the accuracy. Honestly, I'm not sure but it APPEARS that it gives me the total hours almost correctly, then rounds the minutes to the nearest hour. so even though I might have 297 hours and 25 minutes, it will only give me the 298. If the result is supposed to be 299:10, I get 299 (I think it's rounding...but I'm not sure exactly what it's doing because when i do the addition by hand it seems a few hours off). I've tried formatting the (...to-date) field to a fixed number with 2 decimals fixed, but that doesn't seem to help either. Sorry for not understanding better...I love letting the program do the work, but I'm truly a novice at this. Thanks for your patience

 

[dhookom]

The last sentence in my previous reply suggested a possible problem with your

=Format([Start Time]-1-[Finish Time],"Short Time")

Don't attempt to Sum() a value you have changed to a string. If you want to Sum() the value, try:
=Sum([Finish Time]-[Start Time])
This assumes the Finish Time is later than the Start Time.

Duane
Hook'D on Access
MS Access MVP

 

[terpdavid]

AHHHH! Got it! thanks dhookom for the clear direction.

 

[terpdavid]

I tried the =sum(finish time) - (start time) ... It still returns the whole number. I'm not sure what I've got wrong at this point

 

[dhookom]

What are the properties of the text box? Did you set the format and decimal places?

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Also, simplify your issue by limiting your Record Source to a few records and test. Please provide us with sample data and displayed results since we can't see what you can see.

Duane
Hook'D on Access
MS Access MVP

 

[terpdavid]

I set the [Total Hours To-Date] field as a fixed number with 2 decimal places

 

[dhookom]

I didn't know you had a "field" named [Total Hours To-Date]. Where is this created and what is the expression?

Can you reply to my most recent request?

Duane
Hook'D on Access
MS Access MVP