Solved

Format field to accept total time data HH:nn

Posted on 2013-02-06
13
487 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 50

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

749 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