[AccessD] question on report

DorisH3 at aol.com DorisH3 at aol.com
Wed Dec 27 15:19:40 CST 2006


I have a report that summarizes and cannot get it to summarize  properly.
 
I have a group of volunteers that volunteer their time and each one will  
work several events and several days in the office.  I am keeping track of  their 
hours in a special table...that is TimeIn and TimeOut...each volunteer has  a 
number and the only thing I keep in this table is VolunteerNum, Date, TimeIn, 
 TimeOut and the EventNumber.  I have a query, thanks to A.D.Tejpal, that 
uses the  following fields: VolunteerNum, VolunteerNm, TimeIn, TimeOut, 
DtWorked(using  Count in the Totals row), then in the Total row used Expression for the 
 following two fields:
ElapsedMinutes: Fn_GetMinutes([TimeInHW],[TimeOutHW]) and  ElapsedTime:  
Fn_FormatMinutes(Fn_GetMinutes([TimeInHW],[TimeOutHW])) 
 
along with VB code as follows:
 
Public Function HoursAndMinutes(interval As Variant) As  String
'***********************************************************************
'  Function HoursAndMinutes(interval As Variant) As String
' Returns time  interval formatted as a hours:minutes  string
'***********************************************************************
Dim  totalminutes As Long, totalseconds As Long
Dim Hours As Long, Minutes As  Long, seconds As Long
If IsNull(interval) = True Then Exit Function
 
Hours = Int(CSng(interval * 24))
 
totalminutes = Int(CSng(interval * 1440))   ' 1440 = 24 hrs * 60  mins
Minutes = totalminutes Mod 60
 
totalseconds = Int(CSng(interval * 86400))  ' 86400 = 1440 * 60  secs
seconds = totalseconds Mod 60
 
If seconds > 30 Then Minutes = Minutes + 1  ' round up the minutes  and
If Minutes > 59 Then Hours = Hours + 1: Minutes = 0 ' adjust  hours
 
HoursAndMinutes = Hours & ":" & Format(Minutes, "00")
End  Function
 
Public Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As  
Date) As  String
'*********************************************************************
'  Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As  
String
' Returns the time elapsed between a starting Date/Time and an  ending
' Date/Time formatted as a string that looks like this:
' "10 days,  20 hours, 30 minutes, 40  seconds".
'*********************************************************************
Dim  interval As Double, str As String, days As Variant
Dim Hours As String,  Minutes As String, seconds As String
If IsNull(dateTimeStart) = True Or  _
IsNull(dateTimeEnd) = True Then Exit Function
 
interval = dateTimeEnd - dateTimeStart
 
days = Fix(CSng(interval))
Hours = Format(interval, "h")
Minutes =  Format(interval, "n")
seconds = Format(interval, "s")
 
' Days part of the string
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str &  IIf(days = 0, "", _
IIf(Hours & Minutes & seconds  <> "000", ", ", " "))
' Hours part of the string
str = str &  IIf(Hours = "0", "", _
IIf(Hours = "1", Hours & " Hour",  Hours & " Hours"))
str = str & IIf(Hours = "0", "", _
IIf(Minutes & seconds <> "00", ", ", " "))
' Minutes part of the  string
str = str & IIf(Minutes = "0", "", _
IIf(Minutes =  "1", Minutes & " Minute", Minutes & " Minutes"))
str = str &  IIf(Minutes = "0", "", IIf(seconds <> "0", ", ", " "))
' Seconds part  of the string
str = str & IIf(seconds = "0", "", _
IIf(seconds = "1", seconds & " Second", seconds & "  Seconds"))
ElapsedTimeString = IIf(str = "", "0", str)
End Function
 
Public Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As Date) As  
String
'*********************************************************************
'  Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As Date) As String
'  Returns the time elapsed in days between a starting Date/Time and
' an ending  Date/Time formatted as a string that looks like this:
' "10 days" or "1  day".
'*********************************************************************
Dim  interval As Double, days As Variant
If IsNull(dateTimeStart) = True Or  _
IsNull(dateTimeEnd) = True Then Exit Function
interval =  dateTimeEnd - dateTimeStart
days = Fix(CSng(interval))
ElapsedDays =  IIf(days = 1, days & " Day", days & " Days")
End Function


Now, in the report I need to summarize each volunteer  doing a count  of the 
days worked, summarize the total hours that they worked, then I need  to 
multiply the workmens comp rate($6.15) by the number of hours they  worked.  Then 
at the end of the report I have to give a grand total for the  hours and grand 
total for the dollars. I have tried several ways to do the  calculation on 
number of hours worked and workmens comp rate but have had no  success.  
 
Thanks in advance for any help that comes my way...I appreciate this list  
that's for sure.
 
Doris



More information about the AccessD mailing list