Calculated field considering 45 labor days

[pauld118]

Hello all,

I have two text boxes in a form called StartDate and TargetDate.

What I'd like to do is when I enter the StartDate, the TargetDate textbox will be filled automatically considering 45 labor days from the StartDate.

So, the date shown in the TargetDate will refer to 45 labor days from the StartDate. Labor days is defined as 5 days a week. I have never done a calculated text box like this, although I know is possible.

Do I have to write VBA code in order to do this or should be an expression from the Builde Expression??

Thanks!

 

[JANR]

You'll have to write your own UDF like this one: http://access.mvps.org/access/datetime/date0006.htm

JR

 

[JANR]

something like this sould work for you:

Function FindEndDate(StartDate As Variant, lngCountDays As Integer) As Date
Dim lngCounter As Integer
Dim tmpStartDate As Date
On Error GoTo ErrorHandel
tmpStartDate = StartDate
 
If IsNull(StartDate) Then
    FindEndDate = Date
    GoTo ExitPoint
End If
 
 Do While lngCounter <= lngCountDays
        Select Case Weekday(tmpStartDate)
            Case Is = 1, 7
                lngCounter = lngCounter
            Case Is = 2, 3, 4, 5, 6
                lngCounter = lngCounter + 1
        End Select
 
    tmpStartDate = tmpStartDate + 1
    Loop
 
FindEndDate = DateAdd("d", lngCounter, StartDate)
ExitPoint:
On Error GoTo 0
Exit Function
ErrorHandel:
MsgBox Err.Description
Resume ExitPoint
End Function

Place the code in a Standard module and to call it, put this in the controlsource of your enddate textbox:

=FindEndDate([StartDate],45)

JR