Subtract time

[turks67 via AccessMonster.com]

How do I subtract time pass midnight.

eg 11:30 pm to 12:30 am =1:00

 

[fredg]

How do I subtract time pass midnight.

eg 11:30 pm to 12:30 am =1:00

Include the date as well as the time in the field.

DateDiff("n",[StartTime],[EndTime])
will return the elapsed time in full minutes.
? DateDiff("h",#8/1/2009 11:30 pm#,#8/2/2009 12:35 am#)
65

DateDiff("h",[StartTime],[EndTime])
will return the elapsed full hours.
? DateDiff("h",#8/1/2009 11:30 pm#,#8/2/2009 12:35 am#)
1

 

[turks67 via AccessMonster.com]

What am I doing wrong? It's giving me -23.

How do I subtract time pass midnight.

eg 11:30 pm to 12:30 am =1:00

Include the date as well as the time in the field.

DateDiff("n",[StartTime],[EndTime])
will return the elapsed time in full minutes.
? DateDiff("h",#8/1/2009 11:30 pm#,#8/2/2009 12:35 am#)
65

DateDiff("h",[StartTime],[EndTime])
will return the elapsed full hours.
? DateDiff("h",#8/1/2009 11:30 pm#,#8/2/2009 12:35 am#)
1

 

[John W. Vinson]

What am I doing wrong? It's giving me -23.

12:30 am IS 23 hours before 11:30 pm.

An Access Date/Time field is stored as a double float count of days and
fractions of a day (times) since midnight, December 30, 1899. If you have just
a Time field then it is some specific point of time on that long-ago day.
12:30am is actually #12/30/1899 00:30:00#; 11:30 pm is actually
#12/30/1899 23:30:00#.

If you cross midnight, then you must either store both the date AND the time
or go to some artificial contortions to tell Access "No, I mean this time to
be AFTER this other time, not before". If you store the date, then you will
find that #08/02/2009 23:30:00# is in fact one hour before #08/03/2009
00:30:00#.

 

[Linq Adams via AccessMonster.com]

As Fred and John have stated, using a date and time is the correct way to do
this, but if you simply have to use just times, and you'll never be going
past Midnight more than once, here's some code for the "artificial
contortions" John mentioned:

If StopTime <= StartTime Then
Me.TimeInMinutes = DateDiff("n", StartTime, StopTime) + 1440
Else
Me.TimeInMinutes = DateDiff("n", StartTime, StopTime)
End If