How to calulate time

[Joe]

I am building a database to track appointment adherence for a warehouse. I
have 6 fields in which I need to look at [Appointment Date], [Appointment
Time], [Clock in Date], [Clock in Time], [Clock out date], and [Clock out
time].

I need an expression to give me the total hours of detention based on the
below:

If a driver is clocked in before or no more than 15 minutes after his
appointment time. Then he is eligible for detention which starts after 2
hours of his appointment time and date or his clock in time if he was clocked
in within the 15 minute time window.

If the driver showed up more than 15 minutes late for his appointment then
there is no detention time accumulated.

I am new to access and this expression is beyond me. I have so far:

=IIf([Appointment Date]+[Appointment Time]>=[Clock In Date]+[Clock In
Time],(DateDiff("n",[Appointment Date]+[Appointment Time],[Clock Out
Date]+[Clock Out TIme])),0)

But I am getting an Error# in the field unless the driver was late. Thank
you for any help I can get.

 

[Wayne-I-M]

Hi Joe

Best bet in you are building the application now would be to to have just
one field in the table with Date "and" time - rather than 2 fields.
You can always display the date or time in reports/querys and forms if you
want so there is no reason to have 2 fields
It will make things much better once the application is up and running

 

[Joe]

Wayne,

Thank you for the advise, I have corrected that, but am still having issues
with getting the calculation to work. Any Ideas or a way to get the
expression correct?

Hi Joe

Best bet in you are building the application now would be to to have just
one field in the table with Date "and" time - rather than 2 fields.
You can always display the date or time in reports/querys and forms if you
want so there is no reason to have 2 fields
It will make things much better once the application is up and running


--
Wayne
Manchester, England.

I am building a database to track appointment adherence for a warehouse. I
have 6 fields in which I need to look at [Appointment Date], [Appointment
Time], [Clock in Date], [Clock in Time], [Clock out date], and [Clock out
time].

I need an expression to give me the total hours of detention based on the
below:

If a driver is clocked in before or no more than 15 minutes after his
appointment time. Then he is eligible for detention which starts after 2
hours of his appointment time and date or his clock in time if he was clocked
in within the 15 minute time window.

If the driver showed up more than 15 minutes late for his appointment then
there is no detention time accumulated.

I am new to access and this expression is beyond me. I have so far:

=IIf([Appointment Date]+[Appointment Time]>=[Clock In Date]+[Clock In
Time],(DateDiff("n",[Appointment Date]+[Appointment Time],[Clock Out
Date]+[Clock Out TIme])),0)

But I am getting an Error# in the field unless the driver was late. Thank
you for any help I can get.

 

[Joe]

I have figured out the formula, but now when I enter it into the control
source it does not display the information in my table. Any
suggestions/solutions?

Here is my formula:
=IIf(([Appointment Date]+[Appointment Time]+0.0104)>=([Clock In Date]+[Clock
In Time]),([Clock Out Date]+[Clock Out TIme])-(IIf(([Clock In Date]+[Clock In
Time])<=([Appointment Date]+[Appointment Time]),([Appointment
Date]+[Appointment Time]),([Clock In Date]+[Clock In Time])))-0.083,0)

Wayne,

Thank you for the advise, I have corrected that, but am still having issues
with getting the calculation to work. Any Ideas or a way to get the
expression correct?

Hi Joe

Best bet in you are building the application now would be to to have just
one field in the table with Date "and" time - rather than 2 fields.
You can always display the date or time in reports/querys and forms if you
want so there is no reason to have 2 fields
It will make things much better once the application is up and running


--
Wayne
Manchester, England.

I am building a database to track appointment adherence for a warehouse. I
have 6 fields in which I need to look at [Appointment Date], [Appointment
Time], [Clock in Date], [Clock in Time], [Clock out date], and [Clock out
time].

I need an expression to give me the total hours of detention based on the
below:

If a driver is clocked in before or no more than 15 minutes after his
appointment time. Then he is eligible for detention which starts after 2
hours of his appointment time and date or his clock in time if he was clocked
in within the 15 minute time window.

If the driver showed up more than 15 minutes late for his appointment then
there is no detention time accumulated.

I am new to access and this expression is beyond me. I have so far:

=IIf([Appointment Date]+[Appointment Time]>=[Clock In Date]+[Clock In
Time],(DateDiff("n",[Appointment Date]+[Appointment Time],[Clock Out
Date]+[Clock Out TIme])),0)

But I am getting an Error# in the field unless the driver was late. Thank
you for any help I can get.