Solved

Calculate number of hours using two field

Posted on 2014-04-16
9
319 Views
Last Modified: 2014-04-16
I have a form which has two fields.  They are "StartTime" and "StopTime".

In the bound table I have a calculated field named "ElapsedTime".  I want to have that field populate with [StopTime] - [StartTime] in hours.

If StartTime for example is "4/16/2014 2:53:50 PM"
and
If StopTime for example is "4/16/2014 3:01:05 PM"

then the elapsed hours would be something like .13 right?  

Anyway, how can I make the calculated field get the right result?  I have it setup as Long Integer, Standard, 2 decimals but it isn't working correctly.

--Steve
0
Comment
Question by:SteveL13
9 Comments
 
LVL 35

Expert Comment

by:Bembi
ID: 40005079
Long integer values do not have decimals. And as time value are measured in days, an integer can not calculate hours. Hours are calculated as parts of a day, let say 6am is 0,25 and 6pm is 0,75. THe difference is 0,5 means half a day = 12h.
If you use single or double as field type, it should work.
0
 
LVL 13

Expert Comment

by:akb
ID: 40005237
Set the field as General or as a Number if you want to fix decimal points.
Multiply the answer by 24 to get hours.
Or multiply the answer by 1440 to get minutes. 1440 = 24 * 60.
0
 

Author Comment

by:SteveL13
ID: 40005265
This isn't quite getting it.  Again:

If StartTime for example is "4/16/2014 2:53:50 PM"
and
If StopTime for example is "4/16/2014 3:01:05 PM"

then the elapsed hours would be something like .13 right?

How do I get the calculated field to read .13 ?
0
 
LVL 13

Expert Comment

by:akb
ID: 40005285
See attached spreadsheet
Hours.xlsx
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40005313
For ElapsedTime you can use this expression as ControlSource:

=([StopTime]-[StartTime])*24

and apply this for the Format property:

0.00

/gustav
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40005318
You are fast!

/gustav
0
 
LVL 13

Expert Comment

by:akb
ID: 40005331
Isn't that exactly what I gave you in the spreadsheet??
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40005344
Well, this is Access, and rounding was missing.

/gustav
0
 
LVL 13

Expert Comment

by:akb
ID: 40005367
Sorry. Don't know where I got Excel from!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
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.

914 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

18 Experts available now in Live!

Get 1:1 Help Now