Hello -

I am having trouble figuring out how to code a calculated field in a
query to determine a turn time on a process.

I have the start time and the end time as fields in a table.

One of the problems is that I need to calculate this turn time based
on only the hours that the team is operating. (Teams operate from 8 am
to 7 pm so I need to exclude the hours from 7 pm to 8 am in my turn
time.)

The turn time also needs to be calculated differently based on my
start time. If my start time is between 8 am and 3 pm, the goal is 4
hours, and the process will probably be over that same day. If the
start time is between 3 pm and 5 pm, the goal is 4 hours, however the
process will probably not be over until the next day. If the start
time is between 5 pm and 8 am (the next day), the goal is 4 hours from
8 am the next day, and the process will almost always be completed the
next day.

The turn time is not hard as long as the process' start time and end
time are on the same day. The problem comes in when they are on
different days and I need to exclude the hours that we are
non-operational. I know how to do this when I need to exclude days
(datediff([start date], [end date]) - datediff([start date], [end
date], [which ever day I need to exclude weekends for example))) but I
don't think that this is possible with hours of the day (QUESTION #1
--> If it is possible that would answer my question right there if
someone could explain that to me).

My thought was if the start time + end time were on different to
create a net time that was calculated by subtracting the start time
from one of the time constraints above and then subtracting from 8 am
on the day of the end time. Thus is something was received at 4:30,
the net start time would be 7:30 am because 5 minus 4:30 is 30 mins,
and 8 am - 30 mins is 7:30 am. The problem I am trying to work around
here is what to compare the start time to. I would like to compare
just the time part of the start timestamp to a hard coded time in my
query, but am unsure how to do this. I think Access has to have both a
date and time for its date/time field and don't know how to compare
just the timepart of the start timestamp to say #no date 5:00:00 PM#.
(QUESTION # 2--> If this is possible and someone could explain how
this would also be an answer to my question)

Hopefully someone can understand what I am trying to do here (It took
me about 2 hours to get my head around just what I needed to measure
so I may not have explained it well enough here) and has either the
answer to either of my questions above or has some other way of
calculating this process turn time.

Thanks in Advance for any help you can offer me!

Phil:

This stuff is hard, so it is no wonder you are having problems.

I'm clear on your Question 2 and there are a number of ways.
To keep it simple:

dim Timepart as Time

Timepart = TimeValue(now())

In this case you would get the current time.

The arguement for TimeValue can have a date in it, or be a string
containing a time.

This should allow you to calculate what you need.

Good luck
Ira Solomon

On 25 May 2004 06:30:35 -0700, phil_doughty (AT) hotmail (DOT) com (Phil Doughty)
wrote:

Thank you very much Ira, that did the trick. I used a combination of
that and CDate to split all my timestamps into separate date and time
fields and compared them that way. The result was about a 5 nested iif
statement, but it works.

Thanks again!

Ira Solomon <isolomon (AT) solomonltd (DOT) com> wrote