[AccessD] question on report

Doris,

    Apparently, you wish to incorporate some additional features, subsequent to your earlier thread. It would be helpful to have a look at the latest state of your db.

    If you send a zipped copy of your file along with self contained note, I could look into it.

Best wishes,
A.D.Tejpal
---------------

  ----- Original Message ----- 
  From: DorisH3 at aol.com 
  To: accessd at databaseadvisors.com 
  Sent: Thursday, December 28, 2006 02:49
  Subject: [AccessD] question on report


  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