Changing Time

  • Thread starter Thread starter Box 666
  • Start date Start date
B

Box 666

We want to track the turnround time of our orders and aim to respond within
2 hours of receipt. The office is only open between 9am to 5pm.
At the moment customers email in their orders and i have linked Outlook to
our orders db. This gives details of the order plus the time it was received
in Outlook.

The received date / time is held as general date ie dd/mm/yyyy hh/mm/ss.
What I want to do is any items received after 17:00 I want to update / reset
the arrived time as 17:00 and any items received before 09:00 I want to
reset the arrival time to 09:00

What is the best way to achieve these amended times.

Bob
 
Personally, I would prefer to leave the received date / time as it is and
simply calculate the Response Time according to the logic you need.
 
You need to provide a little more detail. Are you saying anything between
17:00 and midnight should be 17:00, and anything between midnight and 09:00
should be 09:00?

You could use something like:

IIf((TimeValue([ReceivedDate]) > #17:00:00#) AND (TimeValue([ReceivedDate])
< #23:59:59# ), DateValue([ReceivedDate]) + #17:00:00#,
IIf((TimeValue([ReceivedDate]) > #00:00:01#) AND (TimeValue([ReceivedDate])
< #09:00:00# ), DateValue([ReceivedDate]) + #09:00:00#, [ReceivedDate]))
 
Excellent, thank you just what I wanted

Bob


Douglas J. Steele said:
You need to provide a little more detail. Are you saying anything between
17:00 and midnight should be 17:00, and anything between midnight and 09:00
should be 09:00?

You could use something like:

IIf((TimeValue([ReceivedDate]) > #17:00:00#) AND (TimeValue([ReceivedDate])
< #23:59:59# ), DateValue([ReceivedDate]) + #17:00:00#,
IIf((TimeValue([ReceivedDate]) > #00:00:01#) AND (TimeValue([ReceivedDate])
< #09:00:00# ), DateValue([ReceivedDate]) + #09:00:00#, [ReceivedDate]))


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Box 666 said:
We want to track the turnround time of our orders and aim to respond within
2 hours of receipt. The office is only open between 9am to 5pm.
At the moment customers email in their orders and i have linked Outlook to
our orders db. This gives details of the order plus the time it was received
in Outlook.

The received date / time is held as general date ie dd/mm/yyyy hh/mm/ss.
What I want to do is any items received after 17:00 I want to update / reset
the arrived time as 17:00 and any items received before 09:00 I want to
reset the arrival time to 09:00

What is the best way to achieve these amended times.

Bob
Click to expand...
Click to expand...
 
Back
Top