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

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
Asked:
bobrossi56
  • 4
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try using

         datediff("h",[StartTime],[EndTime]) as Hrs
0
 
bobrossi56Author 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
 
Rey Obrero (Capricorn1)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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
bobrossi56Author 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
 
Rey Obrero (Capricorn1)Commented:
use this
change the "h"  to "n" then divide by 60

             datediff("N",[StartTime],[EndTime])/60
0
 
Rey Obrero (Capricorn1)Commented:
if you want to limit the display to 2 decimal places

   round(datediff("N",[StartTime],[EndTime])/60,2)
0
 
bobrossi56Author Commented:
Thx...works great....
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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