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

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

Yes but how do I get the proper calculation? This is for a timesheet database that I am building.

Debit19

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

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

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

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