Solved

Format field to accept total time data HH:nn

Posted on 2013-02-06
13
475 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38860747
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
ID: 38860816
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38860841
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:mickeyshelley1
ID: 38860958
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38861055
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
ID: 38861176
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38861263
see this sample db

open report
Q-28022452-db.accdb
0
 

Author Comment

by:mickeyshelley1
ID: 38861450
(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
ID: 38863070
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
ID: 38864293
Thank you I will try it..
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38864321
mickeyshelley1,

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








.
0
 

Author Comment

by:mickeyshelley1
ID: 38864394
I just downloaded it thanks Cap
0
 

Author Closing Comment

by:mickeyshelley1
ID: 38864638
Thanks Cap, that is what i needed...
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

774 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