Default value of 5 WorkingDays after other control value

G

Guest

Here are two functions. The first is a variation of your number of working
days. I think this one is a little better. The second is the one you
requested to add 5 days.

In additions to other changes, note I changed <= 5 to < 5. When the counter
gets to 5, you have already added 5 working days.

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer

Dim intCount As Integer

On Error GoTo Err_WorkingDays

intDayCount = Datediff("d",StartDate, EndDate)

Do While StartDate <= EndDate
If WeekDay(StartDate, vbMonday) > 5 Then
intDayCount = intDayCount - 1)
Else
If Not IsNull(dlookup("[HolDate]","Holidays","[HolDate] = " & "'"
& StartDate & "'")) Then
intDayCount = intDayCount -1)
End If
End If
StartDate = Dateadd("d", 1, StartDate)
Loop

WorkingDays = intDayCount

Exit_WorkingDays:
Exit Function

Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim intDayCount as Integer
Dim dtmReturnDate as Date

intDayCount = 0
dtmReturnDate = OriginalDAte
Do Until intDayCount = 5
If WeekDay(dtmReturnDate, vbMonday) < 5 Then
intDayCount = intDayCount + 1)
dtmReturnDate = Dateadd("d",1,dtmReturnDate)
Else
If IsNull(dlookup("[HolDate]","Holidays","[HolDate] = " & "'" &
dtmReturnDate & "'")) Then
intDayCount = intDayCount +1)
dtmReturnDate = Dateadd("d",1,dtmReturnDate)
End If
End If
Loop

Add5WeekDays = OriginalDate



NewbieSupreme said:
I have an MVP-provided fnction I use to determine the number of workdays
taken for several phases of an order. The code is below (as you can see, I
use a Table called Holidays to exclude holiday dates as well):

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolDate] FROM Holidays", dbOpenSnapshot)

StartDate = StartDate + 1
intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function


On a form, I would like to make the default value of the PromisedDate
control equal to 5 working days after the same form's ReceivedDate. This
value would be set in the AfterUpdate event of ReceivedDate. I have a
function (MVP-supplied again, of course) that works in terms of Weekdays,
but it does not use the Holidays table. I tried several permutations using
the Holidays table references from the other function, but my coding
knowledge is not sufficient to add the use of the Holidays table. The
default value setting function is below:


Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim x As Integer
OriginalDate = forms(frmOrderEntry)!DateRec
x = 0
Do Until x = 5
DayOfWeek = Weekday(DateAdd("d", 1, OriginalDate))
Debug.Print DayOfWeek
Select Case DayOfWeek
Case 1
OriginalDate = DateAdd("d", 1, OriginalDate)
Case 2 To 6
OriginalDate = DateAdd("d", 1, OriginalDate)
x = x + 1
Debug.Print "x = " & x
Case 7
OriginalDate = DateAdd("d", 1, OriginalDate)
End Select
Loop
Add5WeekDays = OriginalDate


End Function


All I need to do is include the looking up and excluding of date values in
the Holidays table to this function. Can anyone tell me how to do this?

Any help is greatly appreciated, and thanks for reading.
Click to expand...

NewbieSupreme said:
I have an MVP-provided fnction I use to determine the number of workdays
taken for several phases of an order. The code is below (as you can see, I
use a Table called Holidays to exclude holiday dates as well):

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolDate] FROM Holidays", dbOpenSnapshot)

StartDate = StartDate + 1
intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function


On a form, I would like to make the default value of the PromisedDate
control equal to 5 working days after the same form's ReceivedDate. This
value would be set in the AfterUpdate event of ReceivedDate. I have a
function (MVP-supplied again, of course) that works in terms of Weekdays,
but it does not use the Holidays table. I tried several permutations using
the Holidays table references from the other function, but my coding
knowledge is not sufficient to add the use of the Holidays table. The
default value setting function is below:


Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim x As Integer
OriginalDate = forms(frmOrderEntry)!DateRec
x = 0
Do Until x = 5
DayOfWeek = Weekday(DateAdd("d", 1, OriginalDate))
Debug.Print DayOfWeek
Select Case DayOfWeek
Case 1
OriginalDate = DateAdd("d", 1, OriginalDate)
Case 2 To 6
OriginalDate = DateAdd("d", 1, OriginalDate)
x = x + 1
Debug.Print "x = " & x
Case 7
OriginalDate = DateAdd("d", 1, OriginalDate)
End Select
Loop
Add5WeekDays = OriginalDate


End Function


All I need to do is include the looking up and excluding of date values in
the Holidays table to this function. Can anyone tell me how to do this?

Any help is greatly appreciated, and thanks for reading.
Click to expand...
 
N

NewbieSupreme

I have an MVP-provided fnction I use to determine the number of workdays
taken for several phases of an order. The code is below (as you can see, I
use a Table called Holidays to exclude holiday dates as well):

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolDate] FROM Holidays", dbOpenSnapshot)

StartDate = StartDate + 1
intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function


On a form, I would like to make the default value of the PromisedDate
control equal to 5 working days after the same form's ReceivedDate. This
value would be set in the AfterUpdate event of ReceivedDate. I have a
function (MVP-supplied again, of course) that works in terms of Weekdays,
but it does not use the Holidays table. I tried several permutations using
the Holidays table references from the other function, but my coding
knowledge is not sufficient to add the use of the Holidays table. The
default value setting function is below:


Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim x As Integer
OriginalDate = forms(frmOrderEntry)!DateRec
x = 0
Do Until x = 5
DayOfWeek = Weekday(DateAdd("d", 1, OriginalDate))
Debug.Print DayOfWeek
Select Case DayOfWeek
Case 1
OriginalDate = DateAdd("d", 1, OriginalDate)
Case 2 To 6
OriginalDate = DateAdd("d", 1, OriginalDate)
x = x + 1
Debug.Print "x = " & x
Case 7
OriginalDate = DateAdd("d", 1, OriginalDate)
End Select
Loop
Add5WeekDays = OriginalDate


End Function


All I need to do is include the looking up and excluding of date values in
the Holidays table to this function. Can anyone tell me how to do this?

Any help is greatly appreciated, and thanks for reading.
 
N

NewbieSupreme

Klatuu:

Thanks for the post, but the adding 5 days one isn't working. First there
were syntax errors, which went away after I deleted the ) after the 1's.
Now when I use this function, the same date is entered in the DatePromised
field as was entered in the DateRec on the form. I have the After Update
event of the form's DateRec field as:

me.PromisedDate = Add5Weekdays([DateRec])

I also tried:

me.PromisedDate = Add5Weekdays(me.[DateRec])

Is there something wrong with the function, or with my AfterUpdate code?
Don't I need to reference the DateRec field value somewhere in the function?

Thanks again



Klatuu said:
Here are two functions. The first is a variation of your number of
working
days. I think this one is a little better. The second is the one you
requested to add 5 days.

In additions to other changes, note I changed <= 5 to < 5. When the
counter
gets to 5, you have already added 5 working days.

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer

Dim intCount As Integer

On Error GoTo Err_WorkingDays

intDayCount = Datediff("d",StartDate, EndDate)

Do While StartDate <= EndDate
If WeekDay(StartDate, vbMonday) > 5 Then
intDayCount = intDayCount - 1)
Else
If Not IsNull(dlookup("[HolDate]","Holidays","[HolDate] = " &
"'"
& StartDate & "'")) Then
intDayCount = intDayCount -1)
End If
End If
StartDate = Dateadd("d", 1, StartDate)
Loop

WorkingDays = intDayCount

Exit_WorkingDays:
Exit Function

Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim intDayCount as Integer
Dim dtmReturnDate as Date

intDayCount = 0
dtmReturnDate = OriginalDAte
Do Until intDayCount = 5
If WeekDay(dtmReturnDate, vbMonday) < 5 Then
intDayCount = intDayCount + 1)
dtmReturnDate = Dateadd("d",1,dtmReturnDate)
Else
If IsNull(dlookup("[HolDate]","Holidays","[HolDate] = " & "'" &
dtmReturnDate & "'")) Then
intDayCount = intDayCount +1)
dtmReturnDate = Dateadd("d",1,dtmReturnDate)
End If
End If
Loop

Add5WeekDays = OriginalDate



NewbieSupreme said:
I have an MVP-provided fnction I use to determine the number of workdays
taken for several phases of an order. The code is below (as you can see,
I
use a Table called Holidays to exclude holiday dates as well):

Public Function WorkingDays(StartDate As Date, EndDate As Date) As
Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolDate] FROM Holidays",
dbOpenSnapshot)

StartDate = StartDate + 1
intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function


On a form, I would like to make the default value of the PromisedDate
control equal to 5 working days after the same form's ReceivedDate. This
value would be set in the AfterUpdate event of ReceivedDate. I have a
function (MVP-supplied again, of course) that works in terms of Weekdays,
but it does not use the Holidays table. I tried several permutations
using
the Holidays table references from the other function, but my coding
knowledge is not sufficient to add the use of the Holidays table. The
default value setting function is below:


Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim x As Integer
OriginalDate = forms(frmOrderEntry)!DateRec
x = 0
Do Until x = 5
DayOfWeek = Weekday(DateAdd("d", 1, OriginalDate))
Debug.Print DayOfWeek
Select Case DayOfWeek
Case 1
OriginalDate = DateAdd("d", 1, OriginalDate)
Case 2 To 6
OriginalDate = DateAdd("d", 1, OriginalDate)
x = x + 1
Debug.Print "x = " & x
Case 7
OriginalDate = DateAdd("d", 1, OriginalDate)
End Select
Loop
Add5WeekDays = OriginalDate


End Function


All I need to do is include the looking up and excluding of date values
in
the Holidays table to this function. Can anyone tell me how to do this?

Any help is greatly appreciated, and thanks for reading.
Click to expand...

NewbieSupreme said:
I have an MVP-provided fnction I use to determine the number of workdays
taken for several phases of an order. The code is below (as you can see,
I
use a Table called Holidays to exclude holiday dates as well):

Public Function WorkingDays(StartDate As Date, EndDate As Date) As
Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolDate] FROM Holidays",
dbOpenSnapshot)

StartDate = StartDate + 1
intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function


On a form, I would like to make the default value of the PromisedDate
control equal to 5 working days after the same form's ReceivedDate. This
value would be set in the AfterUpdate event of ReceivedDate. I have a
function (MVP-supplied again, of course) that works in terms of Weekdays,
but it does not use the Holidays table. I tried several permutations
using
the Holidays table references from the other function, but my coding
knowledge is not sufficient to add the use of the Holidays table. The
default value setting function is below:


Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim x As Integer
OriginalDate = forms(frmOrderEntry)!DateRec
x = 0
Do Until x = 5
DayOfWeek = Weekday(DateAdd("d", 1, OriginalDate))
Debug.Print DayOfWeek
Select Case DayOfWeek
Case 1
OriginalDate = DateAdd("d", 1, OriginalDate)
Case 2 To 6
OriginalDate = DateAdd("d", 1, OriginalDate)
x = x + 1
Debug.Print "x = " & x
Case 7
OriginalDate = DateAdd("d", 1, OriginalDate)
End Select
Loop
Add5WeekDays = OriginalDate


End Function


All I need to do is include the looking up and excluding of date values
in
the Holidays table to this function. Can anyone tell me how to do this?

Any help is greatly appreciated, and thanks for reading.
Click to expand...
Click to expand...
 
G

Guest

My apologies. I had not tested it. I did and this works. There were two
problems. I had a syntax error in my Dlookup call, and I was returning
OriginalDate instead of ReturnDate

Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim intDayCount As Integer
Dim dtmReturnDate As Date
intDayCount = 0
dtmReturnDate = OriginalDate
Do Until intDayCount = 5
If Weekday(dtmReturnDate, vbMonday) < 5 Then
intDayCount = intDayCount + 1
dtmReturnDate = DateAdd("d", 1, dtmReturnDate)
Else
If IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = " &
dtmReturnDate)) Then
intDayCount = intDayCount + 1
dtmReturnDate = DateAdd("d", 1, dtmReturnDate)
End If
End If
Loop

Add5WeekDays = dtmReturnDate
End Function


NewbieSupreme said:
Klatuu:

Thanks for the post, but the adding 5 days one isn't working. First there
were syntax errors, which went away after I deleted the ) after the 1's.
Now when I use this function, the same date is entered in the DatePromised
field as was entered in the DateRec on the form. I have the After Update
event of the form's DateRec field as:

me.PromisedDate = Add5Weekdays([DateRec])

I also tried:

me.PromisedDate = Add5Weekdays(me.[DateRec])

Is there something wrong with the function, or with my AfterUpdate code?
Don't I need to reference the DateRec field value somewhere in the function?

Thanks again



Klatuu said:
Here are two functions. The first is a variation of your number of
working
days. I think this one is a little better. The second is the one you
requested to add 5 days.

In additions to other changes, note I changed <= 5 to < 5. When the
counter
gets to 5, you have already added 5 working days.

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer

Dim intCount As Integer

On Error GoTo Err_WorkingDays

intDayCount = Datediff("d",StartDate, EndDate)

Do While StartDate <= EndDate
If WeekDay(StartDate, vbMonday) > 5 Then
intDayCount = intDayCount - 1)
Else
If Not IsNull(dlookup("[HolDate]","Holidays","[HolDate] = " &
"'"
& StartDate & "'")) Then
intDayCount = intDayCount -1)
End If
End If
StartDate = Dateadd("d", 1, StartDate)
Loop

WorkingDays = intDayCount

Exit_WorkingDays:
Exit Function

Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim intDayCount as Integer
Dim dtmReturnDate as Date

intDayCount = 0
dtmReturnDate = OriginalDAte
Do Until intDayCount = 5
If WeekDay(dtmReturnDate, vbMonday) < 5 Then
intDayCount = intDayCount + 1)
dtmReturnDate = Dateadd("d",1,dtmReturnDate)
Else
If IsNull(dlookup("[HolDate]","Holidays","[HolDate] = " & "'" &
dtmReturnDate & "'")) Then
intDayCount = intDayCount +1)
dtmReturnDate = Dateadd("d",1,dtmReturnDate)
End If
End If
Loop

Add5WeekDays = OriginalDate



NewbieSupreme said:
I have an MVP-provided fnction I use to determine the number of workdays
taken for several phases of an order. The code is below (as you can see,
I
use a Table called Holidays to exclude holiday dates as well):

Public Function WorkingDays(StartDate As Date, EndDate As Date) As
Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolDate] FROM Holidays",
dbOpenSnapshot)

StartDate = StartDate + 1
intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function


On a form, I would like to make the default value of the PromisedDate
control equal to 5 working days after the same form's ReceivedDate. This
value would be set in the AfterUpdate event of ReceivedDate. I have a
function (MVP-supplied again, of course) that works in terms of Weekdays,
but it does not use the Holidays table. I tried several permutations
using
the Holidays table references from the other function, but my coding
knowledge is not sufficient to add the use of the Holidays table. The
default value setting function is below:


Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim x As Integer
OriginalDate = forms(frmOrderEntry)!DateRec
x = 0
Do Until x = 5
DayOfWeek = Weekday(DateAdd("d", 1, OriginalDate))
Debug.Print DayOfWeek
Select Case DayOfWeek
Case 1
OriginalDate = DateAdd("d", 1, OriginalDate)
Case 2 To 6
OriginalDate = DateAdd("d", 1, OriginalDate)
x = x + 1
Debug.Print "x = " & x
Case 7
OriginalDate = DateAdd("d", 1, OriginalDate)
End Select
Loop
Add5WeekDays = OriginalDate


End Function


All I need to do is include the looking up and excluding of date values
in
the Holidays table to this function. Can anyone tell me how to do this?

Any help is greatly appreciated, and thanks for reading.
Click to expand...

NewbieSupreme said:
I have an MVP-provided fnction I use to determine the number of workdays
taken for several phases of an order. The code is below (as you can see,
I
use a Table called Holidays to exclude holiday dates as well):

Public Function WorkingDays(StartDate As Date, EndDate As Date) As
Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolDate] FROM Holidays",
dbOpenSnapshot)

StartDate = StartDate + 1
intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function


On a form, I would like to make the default value of the PromisedDate
control equal to 5 working days after the same form's ReceivedDate. This
value would be set in the AfterUpdate event of ReceivedDate. I have a
function (MVP-supplied again, of course) that works in terms of Weekdays,
but it does not use the Holidays table. I tried several permutations
using
the Holidays table references from the other function, but my coding
knowledge is not sufficient to add the use of the Holidays table. The
default value setting function is below:


Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim x As Integer
OriginalDate = forms(frmOrderEntry)!DateRec
x = 0
Do Until x = 5
DayOfWeek = Weekday(DateAdd("d", 1, OriginalDate))
Debug.Print DayOfWeek
Select Case DayOfWeek
Case 1
OriginalDate = DateAdd("d", 1, OriginalDate)
Case 2 To 6
OriginalDate = DateAdd("d", 1, OriginalDate)
x = x + 1
Debug.Print "x = " & x
Case 7
OriginalDate = DateAdd("d", 1, OriginalDate)
End Select
Loop
Add5WeekDays = OriginalDate


End Function


All I need to do is include the looking up and excluding of date values
in
the Holidays table to this function. Can anyone tell me how to do this?

Any help is greatly appreciated, and thanks for reading.
Click to expand...
Click to expand...
Click to expand...
 
N

NewbieSupreme

Klatuu:

I appreciate your help, but this function is now adding 5 days to the
DateRec value, regardless of whether dates are in the Holidays table or
whether it's a Saturday or Sunday. With me.PromisedDate =
Add5Weekdays([DateRec]) as the AfterUpdate code of DateRec, the value that
pops in is always exactly 5 dates after DateRec, including weekends. I need
it to add 5 working days.

Am I doing something wrong?

Thanks again




Klatuu said:
My apologies. I had not tested it. I did and this works. There were two
problems. I had a syntax error in my Dlookup call, and I was returning
OriginalDate instead of ReturnDate

Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim intDayCount As Integer
Dim dtmReturnDate As Date
intDayCount = 0
dtmReturnDate = OriginalDate
Do Until intDayCount = 5
If Weekday(dtmReturnDate, vbMonday) < 5 Then
intDayCount = intDayCount + 1
dtmReturnDate = DateAdd("d", 1, dtmReturnDate)
Else
If IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = " &
dtmReturnDate)) Then
intDayCount = intDayCount + 1
dtmReturnDate = DateAdd("d", 1, dtmReturnDate)
End If
End If
Loop

Add5WeekDays = dtmReturnDate
End Function


NewbieSupreme said:
Klatuu:

Thanks for the post, but the adding 5 days one isn't working. First
there
were syntax errors, which went away after I deleted the ) after the 1's.
Now when I use this function, the same date is entered in the
DatePromised
field as was entered in the DateRec on the form. I have the After Update
event of the form's DateRec field as:

me.PromisedDate = Add5Weekdays([DateRec])

I also tried:

me.PromisedDate = Add5Weekdays(me.[DateRec])

Is there something wrong with the function, or with my AfterUpdate code?
Don't I need to reference the DateRec field value somewhere in the
function?

Thanks again



Klatuu said:
Here are two functions. The first is a variation of your number of
working
days. I think this one is a little better. The second is the one you
requested to add 5 days.

In additions to other changes, note I changed <= 5 to < 5. When the
counter
gets to 5, you have already added 5 working days.

Public Function WorkingDays(StartDate As Date, EndDate As Date) As
Integer

Dim intCount As Integer

On Error GoTo Err_WorkingDays

intDayCount = Datediff("d",StartDate, EndDate)

Do While StartDate <= EndDate
If WeekDay(StartDate, vbMonday) > 5 Then
intDayCount = intDayCount - 1)
Else
If Not IsNull(dlookup("[HolDate]","Holidays","[HolDate] = " &
"'"
& StartDate & "'")) Then
intDayCount = intDayCount -1)
End If
End If
StartDate = Dateadd("d", 1, StartDate)
Loop

WorkingDays = intDayCount

Exit_WorkingDays:
Exit Function

Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim intDayCount as Integer
Dim dtmReturnDate as Date

intDayCount = 0
dtmReturnDate = OriginalDAte
Do Until intDayCount = 5
If WeekDay(dtmReturnDate, vbMonday) < 5 Then
intDayCount = intDayCount + 1)
dtmReturnDate = Dateadd("d",1,dtmReturnDate)
Else
If IsNull(dlookup("[HolDate]","Holidays","[HolDate] = " & "'"
&
dtmReturnDate & "'")) Then
intDayCount = intDayCount +1)
dtmReturnDate = Dateadd("d",1,dtmReturnDate)
End If
End If
Loop

Add5WeekDays = OriginalDate



:

I have an MVP-provided fnction I use to determine the number of
workdays
taken for several phases of an order. The code is below (as you can
see,
I
use a Table called Holidays to exclude holiday dates as well):

Public Function WorkingDays(StartDate As Date, EndDate As Date) As
Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolDate] FROM Holidays",
dbOpenSnapshot)

StartDate = StartDate + 1
intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function


On a form, I would like to make the default value of the PromisedDate
control equal to 5 working days after the same form's ReceivedDate.
This
value would be set in the AfterUpdate event of ReceivedDate. I have a
function (MVP-supplied again, of course) that works in terms of
Weekdays,
but it does not use the Holidays table. I tried several permutations
using
the Holidays table references from the other function, but my coding
knowledge is not sufficient to add the use of the Holidays table. The
default value setting function is below:


Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim x As Integer
OriginalDate = forms(frmOrderEntry)!DateRec
x = 0
Do Until x = 5
DayOfWeek = Weekday(DateAdd("d", 1, OriginalDate))
Debug.Print DayOfWeek
Select Case DayOfWeek
Case 1
OriginalDate = DateAdd("d", 1, OriginalDate)
Case 2 To 6
OriginalDate = DateAdd("d", 1, OriginalDate)
x = x + 1
Debug.Print "x = " & x
Case 7
OriginalDate = DateAdd("d", 1, OriginalDate)
End Select
Loop
Add5WeekDays = OriginalDate


End Function


All I need to do is include the looking up and excluding of date
values
in
the Holidays table to this function. Can anyone tell me how to do
this?

Any help is greatly appreciated, and thanks for reading.




:

I have an MVP-provided fnction I use to determine the number of
workdays
taken for several phases of an order. The code is below (as you can
see,
I
use a Table called Holidays to exclude holiday dates as well):

Public Function WorkingDays(StartDate As Date, EndDate As Date) As
Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolDate] FROM Holidays",
dbOpenSnapshot)

StartDate = StartDate + 1
intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function


On a form, I would like to make the default value of the PromisedDate
control equal to 5 working days after the same form's ReceivedDate.
This
value would be set in the AfterUpdate event of ReceivedDate. I have a
function (MVP-supplied again, of course) that works in terms of
Weekdays,
but it does not use the Holidays table. I tried several permutations
using
the Holidays table references from the other function, but my coding
knowledge is not sufficient to add the use of the Holidays table. The
default value setting function is below:


Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim x As Integer
OriginalDate = forms(frmOrderEntry)!DateRec
x = 0
Do Until x = 5
DayOfWeek = Weekday(DateAdd("d", 1, OriginalDate))
Debug.Print DayOfWeek
Select Case DayOfWeek
Case 1
OriginalDate = DateAdd("d", 1, OriginalDate)
Case 2 To 6
OriginalDate = DateAdd("d", 1, OriginalDate)
x = x + 1
Debug.Print "x = " & x
Case 7
OriginalDate = DateAdd("d", 1, OriginalDate)
End Select
Loop
Add5WeekDays = OriginalDate


End Function


All I need to do is include the looking up and excluding of date
values
in
the Holidays table to this function. Can anyone tell me how to do
this?

Any help is greatly appreciated, and thanks for reading.
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

Klatuu said:
My apologies. I had not tested it. I did and this works. There were two
problems. I had a syntax error in my Dlookup call, and I was returning
OriginalDate instead of ReturnDate

Public Function Add5WeekDays(OriginalDate As Date) As Date
Dim intDayCount As Integer
Dim dtmReturnDate As Date
intDayCount = 0
dtmReturnDate = OriginalDate
Do Until intDayCount = 5
If Weekday(dtmReturnDate, vbMonday) < 5 Then
intDayCount = intDayCount + 1
dtmReturnDate = DateAdd("d", 1, dtmReturnDate)
Else
If IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = " &
dtmReturnDate)) Then
intDayCount = intDayCount + 1
dtmReturnDate = DateAdd("d", 1, dtmReturnDate)
End If
End If
Loop

Add5WeekDays = dtmReturnDate
End Function
Click to expand...


Double check the logic here. It looks like the DateAdd
calls should be outside the If block, right before the Loop
statement.

Also check that the DLookup is working as expected. I think
the condition needs to identify the value as a date value:

If . . . , "[HolDate] = #" & dtmReturnDate) & "#") Then
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Work Days 2
Workdays Function : Repost 2
Calculate businness Days 1
Function "Workingdays" query error date 6
Calculate working days between dates? 14
Calculate Working Days Between Two Dates 2
Viewing Totals/GroupBy creates Data Mismatch 3
Custom Function 6

Top