Access 2007
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",[OPENEDDATETI
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
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",[OPENEDDATETI
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
Try:
? CDate(#2014-10-06 14:52:27# - #2014-10-06 08:10:07#)
=> 06:42:20
/gustav
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.
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.
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