[AccessD] Question on hours

Doris,

    When only time part is entered in a date/time field, assumed date part for both today and the day after (in fact, all days) becomes the same. Such a situation is not well suited for flat application of DateDiff() function

    Function named Fn_GetMinutes() as given below, should get you the time elapsed (in minutes). As the result returned by this function is a numerical value, it can be used in any mathematical operation as desired.

    Incidentally, for converting the elapsed minutes into a string  formatted as Hrs:Mins, function Fn_FormatMinutes() as given below, can be used. The result of function  Fn_GetMinutes() can be fed into this function, so as to display the time in Hrs:Mins format. (This formatted string is however not meant to be used for any further calculations).

Best wishes,
A.D.Tejpal

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
==============================

  ----- Original Message ----- 
  From: DorisH3 at aol.com 
  To: accessd at databaseadvisors.com 
  Sent: Tuesday, December 05, 2006 09:30
  Subject: [AccessD] Question on hours


  I have a table that maintains the Volunteer Number, Time In, Time Out, Date Worked.  I have put together a report using a query that shows the  volunteers name, volunteer number, date worked, time in, time out.  On the  report I show all of the fields that are contained in the query along with a  calculation TimeOut - TimeIn which I named Hours.  My report looks like  this
   
  Name               Vol#     Date              TimeIn        TimeOut        
  Hours
  Alice White     1099
                                   10/1/06         4:00  PM     7:00  PM        
    3
                                    10/5/06         4:00  PM     7:00  PM       
     3
   
  10/7/06         4:00  PM     7:00  PM          3
                                    10/10/06       4:00  PM     7:00  PM        
    3
   
  In the group footer I did a calculation as follows =Sum([Hours])  and  in the report footer I did the same.
   
  It appears that anything under 24 hours calculates OK, anything over 24  hours is incorrect....can anyone help me...I am a novice at this so I apologize  ahead of time.
   
  Doris