Prev: how to build expression with conditins and totals
Next: Creating duplicate rows
From: John W. Vinson on 26 Feb 2010 13:56
On Fri, 26 Feb 2010 13:38:50 GMT, "auujxa2 via AccessMonster.com" <u37567(a)uwe>
wrote:

>I've exhausted my research ability on this one. Please help!
>
>I have StartTime1, EndTime1, TotalTime1. (through 7 for a full week)

Then you have an incorrectly normalized database. You should have one record
for each day.

>The start and end times are medium time, because my customer doesn't want
>military short time. But I have total time at short time, using the datediff,
>"n" /1440 function.

The format is ABSOLUTELY IRRELEVANT. See below.

>And everything so far works great.
>
>But the Grand total line, which sums TotalTime1:TotalTime7 "crashes" after
>the sum is more than 24 hours.
>
>Suggestions please!!

A date/time value is stored as a Double Float number, a count of days and
fractions of a day (times) since midnight, December 30, 1899. The format just
controls how that number is displayed, not what's stored; you can have a wide
variety of different formats.

As such, a Date/Time field is best used for storing specific points in time.
1.25 is actually equivalent to #12/31/1899 06:00:00# - not 30:00, and there is
no format that will display it as 30:00.

I would suggest calculating your durations as integer minutes - NOT as
date/time values; you can *display* the duration by using an expression like

Duration \ 60 & ":" & Duration MOD 60, "00")

where duration is an integer number of minutes.
--

John W. Vinson [MVP]
From: John W. Vinson on 26 Feb 2010 14:01
On Fri, 26 Feb 2010 16:22:14 GMT, "auujxa2 via AccessMonster.com" <u37567(a)uwe>
wrote:

>Thanks Allen. My grand total is giving me a concatenation of all 7 totals.
>(exmaple: 6:305:308:459:45)
>
>here is one of 7 totals that are summed.
>=DateDiff("n",[txtStartTime1],[txtEndTime1])\60 & Format(DateDiff("n",
>[txtStartTime1],[txtEndTime1]) Mod 60,"\:00")

If you insist on using seven fields in one record rather than a correctly
normalized one-record-per-day, then add up the times and format the sum:

(DateDiff("n",[txtStartTime1],[txtEndTime1]) +
DateDiff("n",[txtStartTime2],[txtEndTime2]) +
DateDiff("n",[txtStartTime3],[txtEndTime3]) <etc>)
MOD 60 & Format(DateDiff("n",[txtStartTime1],[txtEndTime1]) +
DateDiff("n",[txtStartTime2],[txtEndTime2]) +
DateDiff("n",[txtStartTime3],[txtEndTime3]) <etc>) MOD 60, "\:00")

--

John W. Vinson [MVP]
First  |  Prev  | 
Pages: 1 2
Prev: how to build expression with conditins and totals
Next: Creating duplicate rows