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
