[AccessD] Question on hours Att A D Tejpal

dorish3 at aol.com dorish3 at aol.com
Mon Dec 11 20:19:17 CST 2006


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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
________________________________________________________________________
Check out the new AOL.  Most comprehensive set of free safety and security tools, free access to millions of high-quality videos from across the web, free AOL Mail and more.



More information about the AccessD mailing list