Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    23

    Can someone help with time calc in access.

    I have an easy question I hope.

    I have a form in access with the following fields DOS (date/time),TypeOfService(text),DayStart(date/time),DayFinish(date/time). Now I want to take the DayFinish-DayStart and get how many hours. I got it to work except for when you start at 10:00PM and end at 12:00AM. The result of this is -22 hours. I tried to concatenate the DOS&DayStart and then DOS&DayFinish and then use DateDiff in a third new text field on the form.

    All of the time related fields I am using date/time and have tried switching the format to general, standard, shorttime,mediumtime,longtime. I have tried looking through several of the reference books I have and have not found any reference on how to do this.

    Can anyone steer me in the right direction.

    Thanks,
    Debit19

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    The problem lies in the fact that if you start at 22:00 (aka 10:00 pm), then 0:00 (aka midnight) of that day is 22 hours ago. You probably mean 00:00 of the following day!

    -PatP

  3. #3
    Join Date
    Jan 2005
    Posts
    23
    Yes but how do I get the proper calculation? This is for a timesheet database that I am building.

    Debit19

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Either record the day correctly as the next day, or you'll need to hardcode something that adds 24 hours if you get a negative value to allow for spanning midnight. The code is behaving correctly, the problem is that the users are seeing 00:00 as comming after 23:59 instead of before 00:01... In other words, the problem is in the people, not the code.

    You can code around this, but I see eduction as the better answer. If you explain to them that if they start working at 2005-02-24 14:00 there isn't any way they can finish by 2005-02-24 12:00 (unless they use time travel or a jet fast enough to run down at least two timezones), they ought to agree with that (at least we hope). Based on that, you ought to be able to explain that they can't start at 22:00 and finish by 00:00 on the same day..

    -PatP

  5. #5
    Join Date
    Jan 2005
    Posts
    23
    Thanks for enlightment. 00:00 is the previous day. Would I put the code in the text box that is trying to calculate the difference in hours and how would I have it add 24 hours if I get a negative value?

    Debit19

  6. #6
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Yuor problem description states that the fields are date/time. If that is the case the user should be putting in the Date as well as the time. So set up an if.

    If dayfinish < daystart Then
    Msgbox "Learn to tell time"
    So on and so forth..
    Darasen

  7. #7
    Join Date
    Jan 2005
    Posts
    23
    Yes your right about telling time, but on our little example a person would enter start time 10:00PM and finish time 12:00AM(next day). I am trying to avoid having to enter an additional date for the finish time. I have one date as follows:

    01/01/2005 10:00 PM 12:00 AM should be ------> 2 Hours

    Thanks,
    Debit19

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •