Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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 March 23rd, 2005, 08:50 AM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Time calculation

Hi! I am new to programming. How do I calculate the total elapsed or consumed time in Access? MS Excel has a custom time format [h]:mm:ss. When the total hours exceed or goes beyond 24 it just accumulates or continues on (ie. 28:04:34).

Is there a similar time format in Access? If none, how do I compute for the total time?

Thanks.

 
Old March 23rd, 2005, 09:20 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

There are quite a number of answers to your question depending on the task you're trying to accomplish.

Try looking in help for the DateDiff function.

Access is also able to simply subtract two dates.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
 
Old March 23rd, 2005, 11:39 AM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

rjweers, thanks for the suggestion but I am sorry if my request was not that clear.

What I am trying to do is to compute for the total duration of all calls per week for our PABX. When I do it in a query, the hours portion would return to 0 (zero) whenever the total hours is greater than 23. For example: if the total duration is 24 hours 13 mins and 10 sec (24:13:10) the result I get is 00:13:10. In Excel I just format the cell as [h]:mm:ss and I get the correct result (24:13:10).

Is there a way I can also do this in Access?

Thank you very much.

 
Old March 23rd, 2005, 04:50 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

When I had a similar problem in an application for calculating departmental telephone usage, I did the calculations in minutes and decimal minutes. I did not experience rollover in the totals.

Rand
 
Old March 23rd, 2005, 10:40 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

I'm afraid I'd go with Rand's idea.

In your query calculate the number of minutes for each record and sum that field. If you have to report in hours, split it out in your report.

Of course you could just query the data from Access into Excel, or use some other kind of Office automation for your calculation.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
 
Old March 24th, 2005, 03:02 AM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok. Great suggestions!
Thanks for sharing your ideas.

:)

 
Old March 24th, 2005, 11:51 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here are the calculations for DecimalMinutes or DecimalHours whichever you prefer. Since seconds give the smallest granularity, they should be used in the DateDiff calculation.

Code:
SELECT DateDiff("s",[TimeOne],[TimeTwo])/60 AS DiffInDecimalMins, DateDiff("s",[TimeOne],[TimeTwo])/3600 AS DiffInDecimalHours
FROM TimeTest;
You will need to change table and field names as appropriate.

Rand




Similar Threads
Thread Thread Starter Forum Replies Last Post
time calculation hozyali Beginning PHP 2 April 9th, 2007 08:02 AM
Time Difference Calculation deepak_7k Access 3 November 29th, 2006 08:36 PM
Time Calculation surendran MySQL 1 June 24th, 2006 01:38 PM
client side time calculation in asp.net gmk51080 Javascript 0 November 30th, 2004 04:07 AM





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