Solved

Format field to accept total time data HH:nn

Posted on 2013-02-06
13
456 Views
Last Modified: 2013-02-07
I have a form that tracks total time spent on a project in hours and min. It contains the start time,  Completed time and Total time fields.  The problem is that I the total time field will only store the value if it is a text field and on the report I need to calculate the sum of all the totaltimes column in the report footer. I cant get it to calculate the text data format.

Starttime  (dateTimeformat)
CompleteTime (DateTimeFormat)
TotalTime (number Format)
Private Sub Command6_Click()
If [Completetime] > [starttime] Then
  TotalTime.Value = Format([Completetime] - [starttime], "hh:nn")
Else
   TotalTime.Value = Format([Completedtime] + 1 - [starttime], "hh:nn")
End If
End Sub

Open in new window

0
Comment
Question by:mickeyshelley1
13 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
set Data type of the total time as Number, Double

with this type, you can easily make calculations

you can display it in hh:nn  with

format([TotalTime],"hh:nn")

use this codes

Private Sub Command6_Click()
If [Completetime] > [starttime] Then
  TotalTime.Value = [Completetime] - [starttime]
Else
   TotalTime.Value = [Completedtime] + 1 - [starttime]
End If
End Sub
0
 

Author Comment

by:mickeyshelley1
Comment Utility
I entered a start time of 08:00 and a completed time of 14:00 and the Totaltime value was 0.25 rather than 6:00
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
that is correct  0.25 is 1/4 of  1 day (24 hrs )

if you do this  

format( 0.25, "h:nn")

it will give you 6:00
0
 

Author Comment

by:mickeyshelley1
Comment Utility
If i leave it as is will it allow me to calculate the total hours and min of all the entries in the total time column in a report?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
do you want to display  hh:nn in the report per record ?
do you want a total value in the format hh:nn too ?

create a query with one column for display and another column for calculations.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
It is very complicated to accurately do math on dates and times stored as text such as "8:30" or "2:15 PM" (not impossible, but when I did this for an Outlook custom form time sheet, the code for converting the text In and Out times for three work shifts into numbers and doing the calculations for various subtotals and totals ran over 50 pages).  I would recommend storing all the date/time values in Date fields, and doing the calculations using these fields, so you can use DateAdd and DateDiff to do the match.  Then you can use the Format function to convert them into the desired formats for display.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
see this sample db

open report
Q-28022452-db.accdb
0
 

Author Comment

by:mickeyshelley1
Comment Utility
(do you want to display  hh:nn in the report per record ?)
The priority is for the form to capture the total hours/min on each day,  in the proper format so that I can display the total time value in hours and mins on the footer of a report. Kinda like a time clock would capture the total hours worked in a pay period
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Always store date and time using data type Date.
Then, for display, apply the format you wish.
Thus:
Private Sub Command6_Click()
  If [Completetime] > [starttime] Then
    TotalTime.Value = [Completedtime] - [starttime]
  Else
    TotalTime.Value = [Completedtime] - [starttime] + 1
  End If
End Sub

Open in new window

Then apply the format h:nn to TotalTime.

When you sum these, the total will probably exceed 24 hours.
Use this function to convert such values to a string of hh:nn
Public Function FormatHourMinute( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String
  
' Returns count of days, hours and minutes of datTime
' converted to hours and minutes as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03# + #20:01#
'   returns: 30:04
'
' 2005-02-05. Cactus Data ApS, CPH.

  Dim strHour       As String
  Dim strMinute     As String
  Dim strHourMinute As String
  
  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute count when needed.
  strMinute = Right("0" & CStr(Minute(datTime)), 2)
  strHourMinute = strHour & strSeparator & strMinute
  
  FormatHourMinute = strHourMinute
  
End Function

Open in new window

For example:
=FormatHourMinute(Sum([TotalTime]))

/gustav
0
 

Author Comment

by:mickeyshelley1
Comment Utility
Thank you I will try it..
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
mickeyshelley1,

did you look at the sample db i uploaded at http:#a38861263 ?








.
0
 

Author Comment

by:mickeyshelley1
Comment Utility
I just downloaded it thanks Cap
0
 

Author Closing Comment

by:mickeyshelley1
Comment Utility
Thanks Cap, that is what i needed...
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now