Solved

Access 2007

Posted on 2014-10-03
8
181 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 35

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 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
Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 
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 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 35

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 35

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

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

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

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

809 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