Solved

Access 2007

Posted on 2014-10-03
8
172 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
8 Comments
 
LVL 34

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 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 50 total points
ID: 40360884
0
 
LVL 49

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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 49

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 34

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 49

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 34

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
Messaging apps are amazing tools with the power to do a lot of good, but the truth is the process of collaborating with coworkers requires relationships established through meaningful communication - the kind of communication that only happens face-…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.

895 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

14 Experts available now in Live!

Get 1:1 Help Now