Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Format field to accept total time data HH:nn

Posted on 2013-02-06
13
Medium Priority
?
500 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 52

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

886 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