If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Calculating time duration

Hello;

Need resources for help on calculating duration of time. Using Access 2003 on an XP platform. I have a report that is fed from a query. The query obtains its data from a table where [StartTime] and [EndTime] are stored as Medium Time format. On the QBE grid I have a column with TotalTime: DateDiff("d",[EndTime],[StartTime]). This returns all zeros. When I use TotalTime: [EndTime]-[StartTime] I get a calculated result of 4:27 (using StartTime of 7:30AM and an EndTime of 11:57AM). I also use another column with Duration: Format([StartTime]-1-[EndTime],"Short Time"), which returns the same result (4:27) (just as a different approach to solving the issue)

In the Detail section of my report I have a bound control ([Duration]), which displays what appears to correct data. In the Detail Section Footer I have a control using the formula =Sum([EndTime]-[StartTime]), which appears to return correct data.

Now the problem… I want to total or sum the Sections (which are days) to arrive at the total time duration for each employee (for any time period specified by the user). I am using a control in the Employee Footer which uses =Sum([TotalTime]). This returns 1.2930555555556. The total time as I add up the detail sections (days) is 31:02 (31 hours and 2 minutes). Is this a format issue, or am I doing it all wrong?

I have seen many many posts and info on calculating time duration but none of the examples I have tried so far have been fruitful. I think I understand that Access stores time as a point-in-time, and in order to calculate duration one needs to convert to a number format. Does that mean that I need to store the converted time in a number format, not Medium date as it is stored now… or, store both, or calculate such in the query, or on the form? If anyone can shed some light on this issue of calculating duration of time I would greatly appreciate it.