Work out target date & time based on values

[Guest]

I have posted this in another newsgroup section, but not sure if i put it in
the right place.

I thought this might have been a quick 'Expression' builder thingy but now i
am starting to think its going to take a bit of VBing to get it to work.

Currently building a db for a service company, they take calls for jobs,
input onto system and pass to whoever.

Every job has a priority code and target completion time.

What i need to do is have a Date and Time field that automatically updates
itself with the target time based on the priority code but excluding weekends
and certain hours of the evening.

Example:

Job 1: Call comes in 5pm Friday afternoon, requires completion within 24hrs
but is not urgent - Target time would be 5pm Monday.

Job 2: Call comes in 5pm Friday afternoon, requires completion within 24hrs
and is urgent - Target time would be 5pm Saturday.

Job 3: Call comes in 7pm Monday evening, requires completion within 4hrs and
is not urgent - Target time would be 11am Tuesday.

Now, with Job 3, the target time is 11am Tuesday, this is because the clock
stops at 9pm in the evening and starts again at 9am, so giving two hours from
when the job came in on the Monday evening and two hours the following
Tuesday when the clock starts ticking again at 9am.

So any help would be great
Many thanks
Scott

 

[Chris2]

I have posted this in another newsgroup section, but not sure if i put it in
the right place.

I thought this might have been a quick 'Expression' builder thingy but now i
am starting to think its going to take a bit of VBing to get it to work.

Currently building a db for a service company, they take calls for jobs,
input onto system and pass to whoever.

Every job has a priority code and target completion time.

What i need to do is have a Date and Time field that automatically updates
itself with the target time based on the priority code but excluding weekends
and certain hours of the evening.

Example:

Job 1: Call comes in 5pm Friday afternoon, requires completion within 24hrs
but is not urgent - Target time would be 5pm Monday.

Job 2: Call comes in 5pm Friday afternoon, requires completion within 24hrs
and is urgent - Target time would be 5pm Saturday.

Job 3: Call comes in 7pm Monday evening, requires completion within 4hrs and
is not urgent - Target time would be 11am Tuesday.

Now, with Job 3, the target time is 11am Tuesday, this is because the clock
stops at 9pm in the evening and starts again at 9am, so giving two hours from
when the job came in on the Monday evening and two hours the following
Tuesday when the clock starts ticking again at 9am.

So any help would be great
Many thanks
Scott

Scott,

There are examples of how to do Work Day math at:
http://www.mvps.org/access/datetime/date0012.htm.

What you'll want to do is review it, and then write extra similar code
to work on hours while skipping non-working hours on weekdays and
those weekends where urgency requires work be done. It'll require
more work to combine the potentially simultaneous weekday and weekend
hours calculations. It actually a minor project all by itself, but
the above code should get you moving in the right direction.


Sincerely,

Chris O.