Solved

Access 2007

Posted on 2014-10-03
8
184 Views
Last Modified: 2014-10-07
I have table with 80 000 records and two dates, want to see the difference in hours, in this format "20.24" with the ".24"
and instead now results only show "20.00"

Dates are in this format "2/14/2014 6:27:37 PM"

and if I use this code:

Expr1: DateDiff("h",[OPENEDDATETIME],[CLOSEDDATETIME])

I get results like this 20.00
Notice: no "tens and hundreds of the hour" after the "."

Any help, appreciated!
My Property for Result of Calculation is Format:Standard
0
Comment
Question by:CABRLU63
[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
8 Comments
 
LVL 36

Accepted Solution

by:
PatHartman earned 300 total points
ID: 40360511
DateDiff() rounds to the unit so you will never get fractions.  To do what you want you need to get the difference in minutes.  Then divide the minutes by 60.  that will give you a result like 21.5 for 21 and a half hours.  If you want it to be 21:30 then you have to do a truncated divide and use the remainder as minutes.  So instead of doing SomeField / 60, it is SomeField \ 60 with a backslash instead of a forward slash.  Then you subtract the result from SomeField to get the remainder and that is the minutes.

Difference: DateDiff("n",[OPENEDDATETIME],[CLOSEDDATETIME]) / 60
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 50 total points
ID: 40360884
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
ID: 40360960
If you want it rounded exactly by 4/5 to the closest hundreds of an hour, you can use Format:

HoursCount: CDbl(Format(([CLOSEDDATETIME] - [OPENEDDATETIME]) * 24, "0.00"))

/gustav
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 50 total points
ID: 40361551
Your expression:
Expr1: DateDiff("h",[OPENEDDATETIME],[CLOSEDDATETIME])

Modify to: You need the format in PatHartman's comment to limit to 2 decimal places.
Expr1: Format(DateDiff("n",[OPENEDDATETIME],[CLOSEDDATETIME])/60, "0.00")
gustav's comment produces the same result, assuming the subtraction defaults to days.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40361572
> .. assuming the subtraction defaults to days.

Not quite sure if you mean it won't work for small differences?

My simple expression will work for any (realistic) time difference.
It will round up or down with a maximum of 18 seconds which matches 1/100 hour.

/gustav
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40363946
Subtracting one date from another defaults to day as the units so DateA - DateB = number of days.  To get any other units, you must use the DateDiff() function.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40363961
Pat, why do you think so? Date/time is not integers but doubles.

Try:

? CDate(#2014-10-06 14:52:27# - #2014-10-06 08:10:07#)
=> 06:42:20

/gustav
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40366987
I know that dates are doubles.

Look what happens if the dates span 1 day.
? cdate(now() - #10/6/14 10:00#)
12/31/1899 5:31:31 AM
And this is two days
? cdate(now() - #10/5/14 10:00#)
1/1/1900 5:33:37 AM
This is two days without reformatting as a date.
? (now() - #10/5/14 10:00#)
 2.23255787037488
And look at this strange one.  The first is 3 hour difference but the second is 15
? cdate(#10/7/14 01:00 AM# - #10/6/14 10:00 PM#)
3:00:00 AM
? cdate(#10/7/14 01:00 AM# - #10/6/14 10:00 AM#)
3:00:00 PM

The reason is because Access is calculating the difference in days with a remainder.  You can't ever get actual hours and the format of the result depends on whether you are using AM/PM or 24-hour time.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

730 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