Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 27th, 2003, 12:52 PM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default Adding time values

Hi gang!

First off, for all those who've sent solution ideas in the past... THANK YOU! The dbase works GREAT!

Now I'm trying to calculate total time worked for various employees. The report lists every job the employee did (within date parameters), the time each job was started and finished, and a "total time spent on the job." I have to sum the "total times" to come up with an overall length of "Time On Task" for the date period specified (sometimes the user will ask for data for a single day.. sometimes for an entire week, month, quarter, etc...). The problem I'm running into is converting the decimal number to actual "clock" hours:minutes. I've noticed that the clock hours sum correctly... e.g., an employee worked 30+hours for a date period, and the Total Time field (summed) shows a number such as 30.235467 (you get the idea). What I need to do is convert that decimal number to display as hh:nn, with the hours intact (even beyond 24 hours!) and the minutes appropriately displayed as minutes, not decimal parts of an hour.

I know I've seen the solution to this issue SOMEWHERE, but for the life of me can't remember where!!

Best wishes, and happy holidays!
Scott
 
Old December 27th, 2003, 06:53 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Scott;

I am sorking on something similar right now. It is actually better to keep rack of time worked in increments of 15 minutes. Then you can just have resources enter their time as .5, .25, 1.75.

Then you can do easier math. there is no way that someone works 1.755468 hours anyway it is 1.75.

Changing 30.235467 to actual clock time may not be completly possible.





Sal
 
Old December 30th, 2003, 01:05 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

This article may offer some assistance

http://support.microsoft.com/?kbid=237958

 
Old January 3rd, 2004, 11:34 PM
Authorized User
 
Join Date: Dec 2003
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to StreetSweaper Send a message via MSN to StreetSweaper
Default

are you gonna do this converion in the database??

If you are doing it in a programming language like java or C#.
You can easily convert the minutes to not be a apart of the hor decimals. If you have solved in the database, can you let me know how you did it?

StreetSweaper

 
Old January 5th, 2004, 01:37 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

I guess I'm not seeing the problem. If someone works 30.235467 hours then that equals 30 hours and ROUND(0.235467 * 60, 0) or 14 minutes.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old January 5th, 2004, 02:01 PM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by SerranoG
 I guess I'm not seeing the problem. If someone works 30.235467 hours then that equals 30 hours and ROUND(0.235467 * 60, 0) or 14 minutes.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Hi! THe problem is my manager wants to see the total time as hh:nn, not xx.xxxxxxxxx I get the total just fine, but have to convert the numbers to the right of the decimal point to get minutes. You mentioned "ROUND" in your reply.... I'm not familiar with that... How would I write language that would take the numbers to the right of the decimal and convert them (x*60,0)?

Thanks!
 
Old January 6th, 2004, 03:12 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

To get the decimal part of a number, you can just subtract the integer part - eg:

Dim dblVar1 as Double
Dim dblVar2 as Double
dblVar1 = 6.4343
dblVar2=dblVar1-Int(dblVar1)

So dblVar2=0.4343

or - to put it another way:
X = 6.42345 - Int(6.42345)
means X = 0.42345



I am a loud man with a very large hat. This means I am in charge
 
Old January 9th, 2004, 02:06 PM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

[quote]Originally posted by Steven
 To get the decimal part of a number, you can just subtract the integer part - eg:

Dim dblVar1 as Double
Dim dblVar2 as Double
dblVar1 = 6.4343
dblVar2=dblVar1-Int(dblVar1)

So dblVar2=0.4343

or - to put it another way:
X = 6.42345 - Int(6.42345)
means X = 0.42345

Steven: Sounds great... but I'm a relative "newbie". Could you give an example how/where I would place that little tidbit of code? We need to be able to calculate this in a report, on the fly. How do I name the variables? After the decimal part has been stripped away from the integer, how do I convert it to hh:nn? Any and all ideas/solutions are welcome here in FRIGID New York!

Scott
 
Old January 11th, 2004, 05:39 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well - If you're just wanting to display it on the form as an integer, you could simply have a text box with the control source being =Int([YourField])
Or, if you need to do some calculations with it, you would probably do it in the record source (i.e. the query), where you'd basically just do the same sort of thing

I am a loud man with a very large hat. This means I am in charge





Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding Time Fields Brendan Bartley Access 3 July 30th, 2006 08:35 PM
Adding Time Brendan Bartley Access 2 January 21st, 2006 03:36 AM
Adding two time values j_karthik72 VBScript 1 April 5th, 2004 06:43 AM
Adding to the time Droopy Classic ASP Basics 3 July 7th, 2003 11:55 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.