[AccessD] Question on hours Att A D Tejpal

Doris,

    You are most welcome! In the query serving as record source for the report, if WMinutes is the name of field representing minutes worked, you can compute the total hours by putting a control in group or report footer, with the following expression:
    = Sum([WMinutes]) / 60

    If you still happen to need any further assistance, you can send a zipped copy of your file to me. I shall try to fix it up for you.

Best wishes,
A.D.Tejpal
(adtp at airtelbroadband.in)

  ----- Original Message ----- 
  From: dorish3 at aol.com 
  To: accessd at databaseadvisors.com 
  Sent: Tuesday, December 12, 2006 07:49
  Subject: Re: [AccessD] Question on hours Att A D Tejpal


  Hello A.D.
   
  Sorry I have not gotten back to you sooner but I have been real sick with a flu for the past several days...now on the road to recovery.
   
  Your VBA module worked just great!!!
   
   Each Volunteer works several times in the month...I also need to sum each of the volunteers hours in my report for that month.  I hate being a pest but I sure need help.
   
  I appreciate your help and patience with me...I just can't thank you enough.
   
  Doris
   
   
  -----Original Message-----
  From: adtp at airtelbroadband.in
  To: accessd at databaseadvisors.com
  Sent: Wed, 6 Dec 2006 1:05 AM
  Subject: Re: [AccessD] Question on hours

  Doris,

      The functions are to be placed in a general module in VBA code. Go to VBA editor window (Press Alt + F11). On the editor menu bar, click Insert > Module. In the resulting module, paste both the functions, click the save icon (or press Ctrl + s) and compile by clicking Debug > Compile on the menu bar (Or press Alt, d, l). Come out of VBA editor (Press Alt + q).

      You can use the functions in queries as well as controls on reports/forms. Much better to use in the query itself. For example, the expression for calculated field showing elapsed time in minutes (as seen in query design grid) will be :

      ElapsedMinutes: Fn_GetMinutes(StartTime, FinishTime)

      (StartTime & FinishTime are the names of respective date/time type fields in the table)

      Similarly, for showing elapsed time formatted as Hrs:mins string, the expression for calculated field (as seen in query design grid) will be : 

      ElapsedTime: Fn_FormatMinutes(Fn_GetMinutes(StartTime, FinishTime))

      Caution: As stated earlier, the formatted string (ElapsedTime) is not to be used in any further mathematical calculations. No such problem for ElapsedMinutes.

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

    ----- Original Message ----- 
    From: DorisH3 at aol.com 
    To: accessd at databaseadvisors.com 
    Sent: Wednesday, December 06, 2006 04:06
    Subject: Re: [AccessD] Question on hours


    Hi,
     
    Sorry that I am being such a pest but I didn't know where I should put this routine which I assume is Visual Basic.  Does it go in a text box control  on the report? I definitely need help with this report....it has to show hours  and minutes...and then it must sum each volunteers total hours and  minutes.
     
    Doris
     
    Fn_GetMinutes()
    (Computes time elapsed in  minutes)
    ==============================
    Function Fn_GetMinutes(ByVal  TimeStart As Variant, _
    ByVal TimeFinish As Variant) As  Single

    Fn_GetMinutes = 0     ' Default
    If IsDate(TimeStart) And  IsDate(TimeFinish) Then
    TimeFinish =  IIf(TimeFinish >= TimeStart, TimeFinish, _
    1 +  TimeFinish)
    Fn_GetMinutes = (TimeFinish -  TimeStart) * 1440
    End If
    End  Function
    ==============================

    Fn_FormatMinutes()
    (Formats  minutes As Hrs:Mins string)
    ==============================
    Function  Fn_FormatMinutes(ByVal Minutes _
    As Variant) As String
    Dim Txt As String, Hrs As Long,  Mins As Long

    Txt = "00:00"     ' Default
    If Minutes > 0 Then
    Hrs = Int(Minutes / 60)
    Mins =  Minutes Mod 60
    Txt = Format(Hrs, "00") & ":"  &  _
    Format(Mins,  "00")
    End If

    ExitPoint:
    Fn_FormatMinutes = Txt

    End Function