Working Days

[Guest]

I have a field on my form that needs to add 10 working days to the date
created field. This is just an unbound control that will add 10 working days
to another field called "date created". Right now I just have this in the
control source of this field: "=[DateCreated]+14". But that just adds 14
days. How do I fix this so it will only add 10 working days?

 

[Guest]

Try the code below in the AfterUpdate event of the start date contorl:

Dim dtStartDate As Date
If Not IsNull(Me.txtStartDate) Then
dtStartDate = Me.txtStartDate
If Weekday(StartDate) = 1 Or Weekday(StartDate) = 7 Then
Me.txtTenDaysLater = DateAdd("d", 12, StartDate)
Else
Me.txtTenDaysLater = DateAdd("d", 14, StartDate)
End If
End If

This code assumes that you have a textbox control named "txtTenDaysLater"
that will receive the calculated date. You can substitute the name of your
own control.
--
HTH

Mr B
email if needed to:
draccess at askdoctoraccess dot com

 

[Guest]

Hi Secret Squirrel,

The dhAddWorkDaysA function will allow you to account for company holidays,
if you include a table of holidays that can be loaded into an array:

Date/Time: Doing WorkDay Math in VBA
http://www.mvps.org/access/datetime/date0012.htm


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Hi Mr. B

I put that code in the correct spot and changed the control names but
nothing is being populated in the "ResponseDate" control. Does the control
source for need to blank? Maybe I'm missing something here but it looked
pretty straight forward.

Try the code below in the AfterUpdate event of the start date contorl:

Dim dtStartDate As Date
If Not IsNull(Me.txtStartDate) Then
dtStartDate = Me.txtStartDate
If Weekday(StartDate) = 1 Or Weekday(StartDate) = 7 Then
Me.txtTenDaysLater = DateAdd("d", 12, StartDate)
Else
Me.txtTenDaysLater = DateAdd("d", 14, StartDate)
End If
End If

This code assumes that you have a textbox control named "txtTenDaysLater"
that will receive the calculated date. You can substitute the name of your
own control.
--
HTH

Mr B
email if needed to:
draccess at askdoctoraccess dot com

I have a field on my form that needs to add 10 working days to the date
created field. This is just an unbound control that will add 10 working days
to another field called "date created". Right now I just have this in the
control source of this field: "=[DateCreated]+14". But that just adds 14
days. How do I fix this so it will only add 10 working days?

 

[Guest]

Hi Tom,
Thanks for the info but I'm a little unsure how I would incorporate that
code with what I'm trying to do. How would I have my unbound control point to
that code?

SS

Hi Secret Squirrel,

The dhAddWorkDaysA function will allow you to account for company holidays,
if you include a table of holidays that can be loaded into an array:

Date/Time: Doing WorkDay Math in VBA
http://www.mvps.org/access/datetime/date0012.htm


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have a field on my form that needs to add 10 working days to the date
created field. This is just an unbound control that will add 10 working days
to another field called "date created". Right now I just have this in the
control source of this field: "=[DateCreated]+14". But that just adds 14
days. How do I fix this so it will only add 10 working days?