Need to set a default date in database field based on another fiel

[akkrug]

I have set a default date in our due date field that is = the received date +
7 days. Is there some way I can set the due date = received date + 7
business days, excluding weekends?

Thanks for the help.

Ken K. - 2191

 

[Klatuu]

Here is a function that will do what you want. To exclude holidays, you will
nedd a holiday table. Mine is named Holidays and has a date field namted
HolDate. You can put it in your Defalut Value property as:
AddWorkDays(ReceivedDate,7)
You may also need to put it in the form Current event.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function

 

[akkrug]

I'm not sure how to add this code to our database. Would it be simpler if I
ignored holidays?

Thanks.

Ken K.
--
akkrug

Here is a function that will do what you want. To exclude holidays, you will
nedd a holiday table. Mine is named Holidays and has a date field namted
HolDate. You can put it in your Defalut Value property as:
AddWorkDays(ReceivedDate,7)
You may also need to put it in the form Current event.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function

I have set a default date in our due date field that is = the received date +
7 days. Is there some way I can set the due date = received date + 7
business days, excluding weekends?

Thanks for the help.

Ken K. - 2191

 

[Klatuu]

The code should go in a standard module so it can be used by other forms or
it can go in the General section of your form module.

If you don't care about holidays, just modify the code to exclude the logic
involved with the holidays table; however, all you need to do is create a
table named Holidays with a field name HolDate. If there are no records in
the table, it will think there are no holidays at all.

I'm not sure how to add this code to our database. Would it be simpler if I
ignored holidays?

Thanks.

Ken K.
--
akkrug

Here is a function that will do what you want. To exclude holidays, you will
nedd a holiday table. Mine is named Holidays and has a date field namted
HolDate. You can put it in your Defalut Value property as:
AddWorkDays(ReceivedDate,7)
You may also need to put it in the form Current event.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function

I have set a default date in our due date field that is = the received date +
7 days. Is there some way I can set the due date = received date + 7
business days, excluding weekends?

Thanks for the help.

Ken K. - 2191

 

[akkrug]

I copied and pasted the code into a module named AddWorkDays and set the
[Received Date] field = . I got the following error when I
tried to add a new record: Compile error: Expected Type Name. Te Public
Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date was displayed in red. Please let me know if you have any idea what I'm
doing wrong (other than trying to use Access!)

Thanks.

akkrug

Here is a function that will do what you want. To exclude holidays, you will
nedd a holiday table. Mine is named Holidays and has a date field namted
HolDate. You can put it in your Defalut Value property as:
AddWorkDays(ReceivedDate,7)
You may also need to put it in the form Current event.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function

I have set a default date in our due date field that is = the received date +
7 days. Is there some way I can set the due date = received date + 7
business days, excluding weekends?

Thanks for the help.

Ken K. - 2191

 

[Klatuu]

You can't have a procedure name (Sub or Function) that is the same name as
the module it is in. Rename your module.

I copied and pasted the code into a module named AddWorkDays and set the
[Received Date] field = . I got the following error when I
tried to add a new record: Compile error: Expected Type Name. Te Public
Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date was displayed in red. Please let me know if you have any idea what I'm
doing wrong (other than trying to use Access!)

Thanks.

akkrug

Here is a function that will do what you want. To exclude holidays, you will
nedd a holiday table. Mine is named Holidays and has a date field namted
HolDate. You can put it in your Defalut Value property as:
AddWorkDays(ReceivedDate,7)
You may also need to put it in the form Current event.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function

I have set a default date in our due date field that is = the received date +
7 days. Is there some way I can set the due date = received date + 7
business days, excluding weekends?

Thanks for the help.

Ken K. - 2191

 

[akkrug]

Sorry to be a bother, but Nobody in our IT area has used modules before so
I'm on my own and I'm not an IT person. Please let me know if you can tell
what I'm doing wrong in the following: The field in the database is Received
Date and I want to add 7 days to this date and enter it in the field [Due
Date] but I would like to skip weekends and holidays if I can.
Public Function AddWorkDays(OriginalDate As [Received Date], DaysToAdd As 7)
As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function
--
akkrug

You can't have a procedure name (Sub or Function) that is the same name as
the module it is in. Rename your module.

I copied and pasted the code into a module named AddWorkDays and set the
[Received Date] field = . I got the following error when I
tried to add a new record: Compile error: Expected Type Name. Te Public
Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date was displayed in red. Please let me know if you have any idea what I'm
doing wrong (other than trying to use Access!)

Thanks.

akkrug

Here is a function that will do what you want. To exclude holidays, you will
nedd a holiday table. Mine is named Holidays and has a date field namted
HolDate. You can put it in your Defalut Value property as:
AddWorkDays(ReceivedDate,7)
You may also need to put it in the form Current event.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function


:

I have set a default date in our due date field that is = the received date +
7 days. Is there some way I can set the due date = received date + 7
business days, excluding weekends?

Thanks for the help.

Ken K. - 2191

 

[akkrug]

I tried again, but still no luck. Here is the text of my latest attempt:
Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
Dim DaystoAdd As Integer
'Determine whether to add or subtract
Select Case DaystoAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = [Received Date]
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday([Received Date], vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & [Received Date] & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = [Received Date]
End If
End If
If intDayCount = 7 Then
Exit Do
End If
[Received Date] = DateAdd("d", intAdd, [Received Date])
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function

I did create the holiday table with a field Holdate. The error I am getting
now is "Compile error Invalid Outside Procedure". Sorry to be so dense, but
Thanks for trying!!
--
akkrug

You can't have a procedure name (Sub or Function) that is the same name as
the module it is in. Rename your module.

I copied and pasted the code into a module named AddWorkDays and set the
[Received Date] field = . I got the following error when I
tried to add a new record: Compile error: Expected Type Name. Te Public
Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date was displayed in red. Please let me know if you have any idea what I'm
doing wrong (other than trying to use Access!)

Thanks.

akkrug

Here is a function that will do what you want. To exclude holidays, you will
nedd a holiday table. Mine is named Holidays and has a date field namted
HolDate. You can put it in your Defalut Value property as:
AddWorkDays(ReceivedDate,7)
You may also need to put it in the form Current event.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function


:

I have set a default date in our due date field that is = the received date +
7 days. Is there some way I can set the due date = received date + 7
business days, excluding weekends?

Thanks for the help.

Ken K. - 2191

 

[Klatuu]

This line is necessary:
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date

That is why you are getting that error.

Let me be more detailed.

First, in your database window, select Modules.
Click on New
Paste the original code I posted into the new Module.
Save the Module. I suggest the name be modDateFunctions

Now, if you are calling the function as I suggested in my earlier post, it
should work for you. Let me know if you have more questions. I promise this
will work when we get it right.

I tried again, but still no luck. Here is the text of my latest attempt:
Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
Dim DaystoAdd As Integer
'Determine whether to add or subtract
Select Case DaystoAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = [Received Date]
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday([Received Date], vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & [Received Date] & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = [Received Date]
End If
End If
If intDayCount = 7 Then
Exit Do
End If
[Received Date] = DateAdd("d", intAdd, [Received Date])
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function

I did create the holiday table with a field Holdate. The error I am getting
now is "Compile error Invalid Outside Procedure". Sorry to be so dense, but
Thanks for trying!!
--
akkrug

You can't have a procedure name (Sub or Function) that is the same name as
the module it is in. Rename your module.

I copied and pasted the code into a module named AddWorkDays and set the
[Received Date] field = . I got the following error when I
tried to add a new record: Compile error: Expected Type Name. Te Public
Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date was displayed in red. Please let me know if you have any idea what I'm
doing wrong (other than trying to use Access!)

Thanks.

akkrug


:

Here is a function that will do what you want. To exclude holidays, you will
nedd a holiday table. Mine is named Holidays and has a date field namted
HolDate. You can put it in your Defalut Value property as:
AddWorkDays(ReceivedDate,7)
You may also need to put it in the form Current event.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function


:

I have set a default date in our due date field that is = the received date +
7 days. Is there some way I can set the due date = received date + 7
business days, excluding weekends?

Thanks for the help.

Ken K. - 2191

 

[akkrug]

I think I'm getting closer, but now my form is displaying an error like the
field doesn't exist. I put the following in the default value for Due
date:AddWorkDays([Received Date],7)
The code for the module is as follows:
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
OriginalDate = [Received Date]
DaysToAdd = 7
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function

Thanks again for your patience!!

Ken K. - 2191



--
akkrug

This line is necessary:
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date

That is why you are getting that error.

Let me be more detailed.

First, in your database window, select Modules.
Click on New
Paste the original code I posted into the new Module.
Save the Module. I suggest the name be modDateFunctions

Now, if you are calling the function as I suggested in my earlier post, it
should work for you. Let me know if you have more questions. I promise this
will work when we get it right.

I tried again, but still no luck. Here is the text of my latest attempt:
Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
Dim DaystoAdd As Integer
'Determine whether to add or subtract
Select Case DaystoAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = [Received Date]
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday([Received Date], vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & [Received Date] & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = [Received Date]
End If
End If
If intDayCount = 7 Then
Exit Do
End If
[Received Date] = DateAdd("d", intAdd, [Received Date])
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function

I did create the holiday table with a field Holdate. The error I am getting
now is "Compile error Invalid Outside Procedure". Sorry to be so dense, but
Thanks for trying!!
--
akkrug

You can't have a procedure name (Sub or Function) that is the same name as
the module it is in. Rename your module.

:

I copied and pasted the code into a module named AddWorkDays and set the
[Received Date] field = . I got the following error when I
tried to add a new record: Compile error: Expected Type Name. Te Public
Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date was displayed in red. Please let me know if you have any idea what I'm
doing wrong (other than trying to use Access!)

Thanks.

akkrug


:

Here is a function that will do what you want. To exclude holidays, you will
nedd a holiday table. Mine is named Holidays and has a date field namted
HolDate. You can put it in your Defalut Value property as:
AddWorkDays(ReceivedDate,7)
You may also need to put it in the form Current event.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function


:

I have set a default date in our due date field that is = the received date +
7 days. Is there some way I can set the due date = received date + 7
business days, excluding weekends?

Thanks for the help.

Ken K. - 2191

 

[akkrug]

Still can't get this to work. Any ideas? If not, I might be able to just go
with all days instead of skipping weekends and holidays.

Thanks for trying!

Ken K. - 2191
--
akkrug

I think I'm getting closer, but now my form is displaying an error like the
field doesn't exist. I put the following in the default value for Due
date:AddWorkDays([Received Date],7)
The code for the module is as follows:
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
OriginalDate = [Received Date]
DaysToAdd = 7
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function

Thanks again for your patience!!

Ken K. - 2191



--
akkrug

This line is necessary:
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date

That is why you are getting that error.

Let me be more detailed.

First, in your database window, select Modules.
Click on New
Paste the original code I posted into the new Module.
Save the Module. I suggest the name be modDateFunctions

Now, if you are calling the function as I suggested in my earlier post, it
should work for you. Let me know if you have more questions. I promise this
will work when we get it right.

I tried again, but still no luck. Here is the text of my latest attempt:
Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
Dim DaystoAdd As Integer
'Determine whether to add or subtract
Select Case DaystoAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = [Received Date]
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday([Received Date], vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & [Received Date] & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = [Received Date]
End If
End If
If intDayCount = 7 Then
Exit Do
End If
[Received Date] = DateAdd("d", intAdd, [Received Date])
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function

I did create the holiday table with a field Holdate. The error I am getting
now is "Compile error Invalid Outside Procedure". Sorry to be so dense, but
Thanks for trying!!
--
akkrug


:

You can't have a procedure name (Sub or Function) that is the same name as
the module it is in. Rename your module.

:

I copied and pasted the code into a module named AddWorkDays and set the
[Received Date] field = . I got the following error when I
tried to add a new record: Compile error: Expected Type Name. Te Public
Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date was displayed in red. Please let me know if you have any idea what I'm
doing wrong (other than trying to use Access!)

Thanks.

akkrug


:

Here is a function that will do what you want. To exclude holidays, you will
nedd a holiday table. Mine is named Holidays and has a date field namted
HolDate. You can put it in your Defalut Value property as:
AddWorkDays(ReceivedDate,7)
You may also need to put it in the form Current event.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function


:

I have set a default date in our due date field that is = the received date +
7 days. Is there some way I can set the due date = received date + 7
business days, excluding weekends?

Thanks for the help.

Ken K. - 2191

 

[Mr-Re Man]

Hi Klatuu, I've ben picking up this thread as it is just what I need, I have
entered the code in a module as you suggested (code below), set up a table
for holidays as suggested, but the only thing it doesn't seem to do is in my
field (ActualResponseDate), the date is not being filled in.

Any ideas?

Public Function AddWorkDays(DateReceived As Date, DaysToAdd As Integer) As
Date
'D Hargis
'DateReceived = First Day to calculate number of working days from
'DaysToAdd = (5) Number of Working Days to add to DateReceived
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = DateReceived
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(DateReceived, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "tblHoliday", _
"[HolDate] = #" & DateReceived & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = DateReceived
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
DateReceived = DateAdd("d", intAdd, DateReceived)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function

This line is necessary:
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date

That is why you are getting that error.

Let me be more detailed.

First, in your database window, select Modules.
Click on New
Paste the original code I posted into the new Module.
Save the Module. I suggest the name be modDateFunctions

Now, if you are calling the function as I suggested in my earlier post, it
should work for you. Let me know if you have more questions. I promise this
will work when we get it right.

I tried again, but still no luck. Here is the text of my latest attempt:
Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
Dim DaystoAdd As Integer
'Determine whether to add or subtract
Select Case DaystoAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = [Received Date]
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday([Received Date], vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & [Received Date] & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = [Received Date]
End If
End If
If intDayCount = 7 Then
Exit Do
End If
[Received Date] = DateAdd("d", intAdd, [Received Date])
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function

I did create the holiday table with a field Holdate. The error I am getting
now is "Compile error Invalid Outside Procedure". Sorry to be so dense, but
Thanks for trying!!
--
akkrug

You can't have a procedure name (Sub or Function) that is the same name as
the module it is in. Rename your module.

:

I copied and pasted the code into a module named AddWorkDays and set the
[Received Date] field = . I got the following error when I
tried to add a new record: Compile error: Expected Type Name. Te Public
Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date was displayed in red. Please let me know if you have any idea what I'm
doing wrong (other than trying to use Access!)

Thanks.

akkrug


:

Here is a function that will do what you want. To exclude holidays, you will
nedd a holiday table. Mine is named Holidays and has a date field namted
HolDate. You can put it in your Defalut Value property as:
AddWorkDays(ReceivedDate,7)
You may also need to put it in the form Current event.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function


:

I have set a default date in our due date field that is = the received date +
7 days. Is there some way I can set the due date = received date + 7
business days, excluding weekends?

Thanks for the help.

Ken K. - 2191