HI all,

I've the following fields in a report which datatype is Date/Time.

Day WorkedHours
Monday 8:00
Tuesday 8:00
Wednesday 8:00
Thursday 7:00
Friday 8:30
Saturday 5:30

TotalWorkedHours=

The Problem is that I want to add the TotalWorkedhours per week in a
report. I used this expression in the COntrol Source=
sum([Workedhours]), but it doesn't work. I was told that maybe because
my fields were Date/time won't add up. Does anybody have any
suggestions?

On 18 Jun 2004 07:38:19 -0700, christian wrote:
[color=blue]
> HI all,
>
> I've the following fields in a report which datatype is Date/Time.
>
> Day WorkedHours
> Monday 8:00
> Tuesday 8:00
> Wednesday 8:00
> Thursday 7:00
> Friday 8:30
> Saturday 5:30
>
> TotalWorkedHours=
>
> The Problem is that I want to add the TotalWorkedhours per week in a
> report. I used this expression in the COntrol Source=
> sum([Workedhours]), but it doesn't work. I was told that maybe because
> my fields were Date/time won't add up. Does anybody have any
> suggestions?[/color]

If the [WorkedHours] field is the number of hours worked in the day,
rather than the TIME of day, then it can't be date/time.
Change the field datatype to Number, Double.

Then you can use
=Sum(Nz([WorkedHours]))
Make sure the control name is not WorkedHours.
The use of the Nz() function will prevent incorrect results if one of
the records summed is Null.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

The time you have isn't a "time". If 8:00 were a time it would be 8 AM.
Instead it is the number of hours between two times, say between 9 AM and 5
PM. Since you are dealing with hours and minutes, I would recommend that
this value be computed in minutes (8 hours would be 480 minutes). You would
then just need to do simple addition on the minutes and convert it to a
"hours:minutes" format for display purposes.

=intMinutes \ 60 & Format(intMinutes Mod 60, "\:00")

--
Wayne Morgan
MS Access MVP


"christian" <csepulveda@partners.org> wrote in message
news:e1cc3cc3.0406180638.799b2430@posting.google.c om...[color=blue]
> HI all,
>
> I've the following fields in a report which datatype is Date/Time.
>
> Day WorkedHours
> Monday 8:00
> Tuesday 8:00
> Wednesday 8:00
> Thursday 7:00
> Friday 8:30
> Saturday 5:30
>
> TotalWorkedHours=
>
> The Problem is that I want to add the TotalWorkedhours per week in a
> report. I used this expression in the COntrol Source=
> sum([Workedhours]), but it doesn't work. I was told that maybe because
> my fields were Date/time won't add up. Does anybody have any
> suggestions?[/color]

csepulveda@partners.org (christian) wrote in message news:<e1cc3cc3.0406180638.799b2430@posting.google. com>...[color=blue]
> HI all,
>
> I've the following fields in a report which datatype is Date/Time.
>
> Day WorkedHours
> Monday 8:00
> Tuesday 8:00
> Wednesday 8:00
> Thursday 7:00
> Friday 8:30
> Saturday 5:30
>
> TotalWorkedHours=
>
> The Problem is that I want to add the TotalWorkedhours per week in a
> report. I used this expression in the COntrol Source=
> sum([Workedhours]), but it doesn't work. I was told that maybe because
> my fields were Date/time won't add up. Does anybody have any
> suggestions?[/color]

Using the following Public functions will allow strSQL below to obtain
the sum you want. Note that in versions of Access after 97 you can
use the Replace function to place a "+" where the ":" is before
appending the "/60". This would eliminate two of the three functions.
If the number of hours to sum is huge a different method should be
used.

strSQL = "SELECT Format$(Sum(ConvertHours(Nz([FieldToSum],"0:0"))),
"$#,##0.00") As ReportSum FROM tblData;"

'------------------
Public Function PreColon(strIn As String) As String
Dim intColon As Integer
PreColon = ""
If Len(strIn) = 0 Then Exit Function
intColon = InStr(1, strIn, ":")
If intColon <= 1 Then Exit Function
PreColon = Left(strIn, intColon - 1)
End Function

Public Function PostColon(strIn As String) As String
Dim intColon As Integer
PostColon = ""
If Len(strIn) = 0 Then Exit Function
intColon = InStr(1, strIn, ":")
If intColon = Len(strIn) Then Exit Function
PostColon = Right(strIn, Len(strIn) - intColon)
End Function

Public Function ConvertHours(strDuration1 As String) As Double
Dim strTemp As String
ConvertHours = 0
If InStr(1, strDuration1, ":", vbTextCompare) = 0 Then Exit Function
strTemp = PreColon(strDuration1) & "+" & PostColon(strDuration1) &
"/60"
ConvertHours = Eval(strTemp)
End Function
'------------------

James A. Fortune