Time calculation

[slundrigan via AccessMonster.com]

On my form 3 fields are Date of Arrival, Time of Arrival, Time of Discharge.
I have a DateDiff expression =DateDiff("n",[Time of Arrival],[Time of
Discharge]) When my Time of Discharge extends into the next day it
calculates incorrectly. i.e. 23:00 and 01:00 should be a difference of 120
minutes. Instead I get -1320. Anyone?

Thanks,

Stan.

 

[Sean]

Unless you tell it otherwise, the comparison will assume that both times are
on the same day; ie: from 1am until 11pm (ie: 22 hours). The following
"should" sort it for you.

strFrom = [Date of Arrival] & [Time of Arrival]

If ([Time of Arrival] < [Time of Discharge]) Then

strTo = DateAdd("d", 1, [Date of Arrival]) & [Time of Departure]

Else

strTo = [Date of Arrival] & [Time of Departure]

End If




Sean

 

[John Vinson]

On my form 3 fields are Date of Arrival, Time of Arrival, Time of Discharge.
I have a DateDiff expression =DateDiff("n",[Time of Arrival],[Time of
Discharge]) When my Time of Discharge extends into the next day it
calculates incorrectly. i.e. 23:00 and 01:00 should be a difference of 120
minutes. Instead I get -1320. Anyone?

Thanks,

Stan.

You have the date of arrival - but you don't have the date of
dixcharge. If you store just a time value in an Access Date/Time
field, Access will store it as a time during December 30, 1899 (the
root of date/time values).

I'd suggest combining the date and time into each field; that way you
can have [ArrivalTime] stored as #12/4/2006 23:00# and [DepartureTime]
as #12/5/2006 01:00#. Your calculations will work correctly, you'll
have one fewer fields to worry about, you can use Now() as the default
value if the data is entered online as the arrival or departure
occurs, you'll be able to handle stays exceeding 24 hours (if that
makes sense in your application), and in general everything will be
simpler.


John W. Vinson[MVP]