• Status: Solved
• Priority: Medium
• Security: Public
• Views: 306

# Access 2003 Duration Calculation

Newbie Access guy....I asked in a pevious post how to calculate time duration so when someone entered 7:00 Am start time and 8:00 am end time Access would know it was 1 hour. The experts told me to use this calc in my query...  24*([EndTime]-[StartTime]) AS Hrs

This works great for the AM hours, but for the PM hours it is reporting a LONG number on my report (based on the query) See the attachment, you can see when I put 18:00 to 19:00 it displays 0.99999999 but when I put in 6:00 to 7:00 it shows 1 hour. On the form the user enteres 6A for 6:00 AM or 3P for 3:00 PM. The forms displays the duration corectly too, but on the AM ones if I click in the duration field (for 9A - 10A) it stays a 1, but if I click in the duration field on a PM then 1 turns to 0.9999999. How can I fix this so it just says 1 even in the PM?
thx experts..BobR
example.jpg
0
bobrossi56
• 4
• 3
1 Solution

Commented:
try using

datediff("h",[StartTime],[EndTime]) as Hrs
0

Author Commented:
Not working...it works for full hours, but not for increments, for example if the time is 9:00 AM to 11:30 AM it only reports 2 hours. The 0.99999 issue has vanished, and it I only use full hours it works, but not picking up the 1/4, 1/2 and 3/4 hours.
0

Commented:
ok, you did not specify that there are fraction of hour parts.
so, now how do you want to display the minute part?
0

Author Commented:
Ya, sometimes the lesson go from 9:00 AM to 10:30 AM so we would want to see 1.5 hours. If they go from 9:00 to 10:15 we'd see 1.25 hrs and 9:00 to 10:45 1.75 hours.
thx
0

Commented:
use this
change the "h"  to "n" then divide by 60

datediff("N",[StartTime],[EndTime])/60
0

Commented:
if you want to limit the display to 2 decimal places

round(datediff("N",[StartTime],[EndTime])/60,2)
0

Author Commented:
Thx...works great....
0

## Featured Post

• 4
• 3
Tackle projects and never again get stuck behind a technical roadblock.