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.
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..
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?
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