Calculating 'due date' from drop-down box

[Someone]

Hello all

I have a database that includes a drop-down box called 'deadline', which
includes four values: 1 hour, 24 hours, 1 working day and 2 working days.
At this stage, I should point out the working week is Monday to Friday. In
addition, each of the four values is assigned a unique number (1-4, funnily
enough).

The main form itself shows the current date and time of the record that is
being logged. I would like, if possible, to get the due date (and time -
this is critically important) to automatically populate in, for example, a
text box, based on whichever of the four options was chosen from the
drop-down box. What I'm finding most difficult is how to omit weekends.

Bearing in mind that I'm not an expert at VBA, I did find a link to this
page http://www.mvps.org/access/datetime/date0012.htm, but I got confused.

Could anyone be so kind to point me in the right direction to help me
resolve this problem?

Many thanks for your time
M

 

[Douglas J. Steele]

Just copy all of that code into a module, then use the following to add,
say, 1 working day to a given date MyDate:

dhAddWorkDaysA(1, MyDate)

You can also take a look at the Access Answers column I wrote for Pinnacle
Publication's Smart Access in September, 2004. You can download the column
(and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Mr M]

Hi Douglas

Thank you very much for helping me. This did the trick. Where you have put
'1', I replaced this with the name of the field, deadline (and MyDate with
LogDate).

Thing is, it appears the date returned is based on the Deadline ID, which is
an autonumber (1-4 as described in my original post). So, for example, if
the LogDate is 4 Nov 05 and the deadline is 2 working days, the date
returned is 10 Nov 05 (4 days plus the weekend).

How can I get the output to base its results on a figure that represents the
actual deadline duration rather than the ID?

Thanks
M

 

[Douglas J. Steele]

You need to put logic in wherever you're calling the code from anyhow, since
you wanted 1 hour and 24 hours as options as well.

You indicated 1 was 1 hour, 2 was 24 hours, 3 was 1 business day and 4 was 2
business days. Your code could be something like

Select Case Me.cboDeadline
Case 1
dtmDueDate = DateAdd("h", 1, LogDate)
Case 2
dtmDueDate = DateAdd("h", 24, LogDate)
Case 3
dtmDueDate = dhAddWorkDaysA(1, LogDate)
Case 4
dtmDueDate = dhAddWorkDaysA(2, LogDate)
Case Else
' Error!
End Select

 

[Someone]

Hi Douglas

Thanks again for your help.

M

You need to put logic in wherever you're calling the code from anyhow, since
you wanted 1 hour and 24 hours as options as well.

You indicated 1 was 1 hour, 2 was 24 hours, 3 was 1 business day and 4 was 2
business days. Your code could be something like

Select Case Me.cboDeadline
Case 1
dtmDueDate = DateAdd("h", 1, LogDate)
Case 2
dtmDueDate = DateAdd("h", 24, LogDate)
Case 3
dtmDueDate = dhAddWorkDaysA(1, LogDate)
Case 4
dtmDueDate = dhAddWorkDaysA(2, LogDate)
Case Else
' Error!
End Select