DateAdd Working Week interval

[littleliz02]

Hi all, I am hoping someone can help..

The fields I have on my form are Initial Contact and Initial Visit.
The Initial Visit should generate the date 10 working days after the manual Contact Date is enetered.

I have used after event update in the Initial Contact box and used the expression:

Initial Visit=DateAdd("w",10,InitialContact).

This worked but it showed the number of days and not the number of working days (Mon-Fri).

I am using Access 2007, could anyone tell me what I need to do to get it to auto generate working days.

I must warn you that I am not a pro, so would be extremely grateful if anyone could help me out.

 

[CJ_London]

I think you are confusing weekday with workday. So far as I am aware there isn't a vba equivalent to workday which can be found in Excel so you'll need to write a suitable algorithm.

How you construct your algorithm will depend on what you consider to be a working week - in simple terms, changing to adding 14 days is the equivalent to your current solution but does not take into account public holidays etc

 

[littleliz02]

Thank you for your reply CJ.

I am working in access and have read threads about codes etc, but these are so confusing, if access had a DateAdd function to add working days instead of just days it would be a great help.

I think we may just have to resort to adding the due date manually.

 

[littleliz02]

I have managed to try and get a little further with my previous request for help, unfortunately it is giving me an error but I think I may nearly be there. I have an expression to add a day to the due date if it falls on a Sat or Sun:

DateInitialInterview=DateAdd("d",10+IIF(weekday=1,1,IIF(weekday=7,2,0)), (DateofContact)

Can anyone tell me where I have gone wrong and why this doesn't work?
It needs to be simple as not very experienced in access.
Thanks

 

[CJ_London]

I think what you are trying to achieve is

Select Case Weekday(DateAdd("d",10, DateofContact)
  Case 1
      DateInitialInterview=DateAdd("d",10+1,DateofContact)
  Case 7
      DateInitialInterview=DateAdd("d",10+2,DateofContact)
  Case Else
      DateInitialInterview=DateAdd("d",10,DateofContact)
End Select

Additional comment - this only applies if the weekdays is a saturday or sunday so not sure if this is what you require - if date of contact is a friday and you add 10 you end up with a Monday so the default 10 days applies which is only 6 working days and still does not account for public holidays etc.

Here is a link which explains more about workdays although I'm not sure it provides the answer you are looking for

http://msdn.microsoft.com/en-us/library/office/dd327646(v=office.12).aspx

 

[missinglinq]

...if access had a DateAdd function to add working days instead of just days it would be a great help...

Somebody at Microsoft agreed with you, and posted this on their site a few years ago:

In a Standard Module enter this:

'**********************************************************
'Declarations section of the module
'**********************************************************

'==========================================================
' The DateAddW() function provides a workday substitute
' for DateAdd("w", number, date). This function performs
' error checking and ignores fractional Interval values.
'==========================================================
Function DateAddW (ByVal TheDate, ByVal Interval)

   Dim Weeks As Long, OddDays As Long, Temp As String

   If VarType(TheDate) <> 7 Or VarType(Interval) < 2 Or _
              VarType(Interval)  > 5 Then
      DateAddW = TheDate
   ElseIf Interval = 0 Then
      DateAddW = TheDate
   ElseIf Interval > 0 Then
      Interval = Int(Interval)

   ' Make sure TheDate is a workday (round down).

      Temp = Format(TheDate, "ddd")
      If Temp = "Sun" Then
         TheDate = TheDate - 2
      ElseIf Temp = "Sat" Then
         TheDate = TheDate - 1
      End If

   ' Calculate Weeks and OddDays.

      Weeks = Int(Interval / 5)
      OddDays = Interval - (Weeks * 5)
      TheDate = TheDate + (Weeks * 7)

  ' Take OddDays weekend into account.

      If (DatePart("w", TheDate) + OddDays) > 6 Then
         TheDate = TheDate + OddDays + 2
      Else
         TheDate = TheDate + OddDays
      End If

      DateAddW = TheDate
   Else                         ' Interval is < 0
      Interval = Int(-Interval) ' Make positive & subtract later.

   ' Make sure TheDate is a workday (round up).

      Temp = Format(TheDate, "ddd")
      If Temp = "Sun" Then
         TheDate = TheDate + 1
      ElseIf Temp = "Sat" Then
         TheDate = TheDate + 2
      End If

   ' Calculate Weeks and OddDays.

      Weeks = Int(Interval / 5)
      OddDays = Interval - (Weeks * 5)
      TheDate = TheDate - (Weeks * 7)

   ' Take OddDays weekend into account.

      If (DatePart("w", TheDate) - OddDays) > 2 Then
         TheDate = TheDate - OddDays - 2
      Else
         TheDate = TheDate - OddDays
      End If

      DateAddW = TheDate
    End If

End Function

Save the Module and name it anything other than DateAddW; giving a Module the same name as a Function that lies within the Module is a sure way to confuse the Access Gnomes!

To invoke this Function simply use:

DateAddW([StartDate],DaysToAdd)

so for your use

Initial Visit = DateAddW(InitialContact,10)

Linq ;0)>

 

[littleliz02]

Hi linq
I appreciate you help, but have become stuck. I created a standard module and named it something other. I chose the after event in the field I wanted to created that held the date (the one I wanted to created the event after) I chose the properties and after event code builder and created a code and typed the code as you advised, unfortunately it is coming back saying:

Compile error: ambiguous name detected DateAddW

Can you advise where I went wrong; I apologise but pretty new to this.

 

[Mihail]

Use the search window (CTRL+F) to find all occurrences for DateAddW in your project (entire project).
The most probably, you have 2 public subs or functions in your code, or you have 2 DIMs in the same routine.