[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