Prev: how to build expression with conditins and totals
Next: Creating duplicate rows
From: auujxa2 via AccessMonster.com on 26 Feb 2010 08:38
I've exhausted my research ability on this one. Please help!

I have StartTime1, EndTime1, TotalTime1. (through 7 for a full week)

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.

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!!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

From: Allen Browne on 26 Feb 2010 09:51
Common question. See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"auujxa2 via AccessMonster.com" <u37567(a)uwe> wrote in message
news:a43633c308f44(a)uwe...
> I've exhausted my research ability on this one. Please help!
>
> I have StartTime1, EndTime1, TotalTime1. (through 7 for a full week)
>
> 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.
>
> 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!!
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1
>
From: Daryl S on 26 Feb 2010 10:34
Auujxa2 -

Time formats cannot show more than 24 hours. Format your GrandTotalTimes (I
assume this is on a report) to be either a numeric field or a date/time
field. You can also have hidden fields that contain the TotalTime for each
record in a decimal hours format, and show your grand totals based on that
field instead of the short time field.

--
Daryl S


"auujxa2 via AccessMonster.com" wrote:

> I've exhausted my research ability on this one. Please help!
>
> I have StartTime1, EndTime1, TotalTime1. (through 7 for a full week)
>
> 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.
>
> 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!!
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1
>
> .
>
From: auujxa2 via AccessMonster.com on 26 Feb 2010 11:22
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")

Allen Browne wrote:
>Common question. See:
> Calculating elapsed time
>at:
> http://allenbrowne.com/casu-13.html
>
>> I've exhausted my research ability on this one. Please help!
>>
>[quoted text clipped - 11 lines]
>>
>> Suggestions please!!

--
Message posted via http://www.accessmonster.com

From: John Spencer on 26 Feb 2010 13:01
Well that expression is returning a STRING and when you use + with strings
they get concatenated into a longer string.

You might try adding the number of minutes BEFORE you attempt to create the
formatted string

DateDiff("n",[txtStartTime1],[txtEndTime1]) +
DateDiff("n",[txtStartTime2],[txtEndTime2])

Once you have summed the minutes, you can then apply the formatting if you
need "hours:Minutes". Of course that is going to be a bit on the unwieldy
side. With just two sets, you end up with something that could look like:

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

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

auujxa2 via AccessMonster.com 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")
>
> Allen Browne wrote:
>> Common question. See:
>> Calculating elapsed time
>> at:
>> http://allenbrowne.com/casu-13.html
>>
>>> I've exhausted my research ability on this one. Please help!
>>>
>> [quoted text clipped - 11 lines]
>>> Suggestions please!!
>
 |  Next  |  Last
Pages: 1 2
Prev: how to build expression with conditins and totals
Next: Creating duplicate rows