Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Format field to accept total time data HH:nn

Posted on 2013-02-06
13
Medium Priority
?
497 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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 51

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

721 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