Calculate working days between dates?

[Guest]

I am trying to calculate the number of days between two dates taking into
consideration holidays and weekends. I am using the following code:

Public Function mdlWorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: mdlWorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************
I have aTable / Form called Recruitment and a Table called tblHolidays.
Within the Table/Form called Recruitment I have a StartDate field, an EndDate
field, and a Days field. I am entering the code as a module named
mdlWorkingDays2 in "Recruitment Database [mdlWorkingDays2 (Code)]. However, I
continue to get a #Name$ error in the Days field. In the ControlSource for my
Days field I have mdlWorkingDays2([StartDate],[EndDate]).

Any ideas what is wrong?

 

[Guest]

In your function, you need

mdlWorkingDays2 = intCount

NOT

WorkingDays2 = intCount

- Dorian

 

[Guest]

Try this.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
'D Hargis
'Calculates the number of working days between two dates
'dtmStart - the first day to include in the range
'dtmEnd - the last day to include in the range
'Returns the number of working days between the two dates
'Both dates are counted if they are working days

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

I am trying to calculate the number of days between two dates taking into
consideration holidays and weekends. I am using the following code:

Public Function mdlWorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: mdlWorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************
I have aTable / Form called Recruitment and a Table called tblHolidays.
Within the Table/Form called Recruitment I have a StartDate field, an EndDate
field, and a Days field. I am entering the code as a module named
mdlWorkingDays2 in "Recruitment Database [mdlWorkingDays2 (Code)]. However, I
continue to get a #Name$ error in the Days field. In the ControlSource for my
Days field I have mdlWorkingDays2([StartDate],[EndDate]).

Any ideas what is wrong?

 

[Douglas J. Steele]

Rename your module.

The module cannot have the same name as any functions or subs in the
database.

 

[Guest]

Thanks to everyone for your suggestions. I will try them out and let you know
what happens. Thanks, Barb

Rename your module.

The module cannot have the same name as any functions or subs in the
database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to calculate the number of days between two dates taking into
consideration holidays and weekends. I am using the following code:

Public Function mdlWorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: mdlWorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************
I have aTable / Form called Recruitment and a Table called tblHolidays.
Within the Table/Form called Recruitment I have a StartDate field, an
EndDate
field, and a Days field. I am entering the code as a module named
mdlWorkingDays2 in "Recruitment Database [mdlWorkingDays2 (Code)].
However, I
continue to get a #Name$ error in the Days field. In the ControlSource for
my
Days field I have mdlWorkingDays2([StartDate],[EndDate]).

Any ideas what is wrong?

 

[Guest]

Well, I tried them, and I am still getting the same #Name$ issue. Any more
ideas?

Thanks to everyone for your suggestions. I will try them out and let you know
what happens. Thanks, Barb

Rename your module.

The module cannot have the same name as any functions or subs in the
database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to calculate the number of days between two dates taking into
consideration holidays and weekends. I am using the following code:

Public Function mdlWorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: mdlWorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************
I have aTable / Form called Recruitment and a Table called tblHolidays.
Within the Table/Form called Recruitment I have a StartDate field, an
EndDate
field, and a Days field. I am entering the code as a module named
mdlWorkingDays2 in "Recruitment Database [mdlWorkingDays2 (Code)].
However, I
continue to get a #Name$ error in the Days field. In the ControlSource for
my
Days field I have mdlWorkingDays2([StartDate],[EndDate]).

Any ideas what is wrong?

 

[Rob Oldfield]

....a couple of ideas...

The control source should start with an =
Where are you defining the function?

Well, I tried them, and I am still getting the same #Name$ issue. Any more
ideas?

Thanks to everyone for your suggestions. I will try them out and let you know
what happens. Thanks, Barb

Rename your module.

The module cannot have the same name as any functions or subs in the
database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am trying to calculate the number of days between two dates taking into
consideration holidays and weekends. I am using the following code:

Public Function mdlWorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: mdlWorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************
I have aTable / Form called Recruitment and a Table called tblHolidays.
Within the Table/Form called Recruitment I have a StartDate field, an
EndDate
field, and a Days field. I am entering the code as a module named
mdlWorkingDays2 in "Recruitment Database [mdlWorkingDays2 (Code)].
However, I
continue to get a #Name$ error in the Days field. In the ControlSource for
my
Days field I have mdlWorkingDays2([StartDate],[EndDate]).

Any ideas what is wrong?

 

[Guest]

I have an = sign at the beginning of the control source, and it is still
coming up with the same error. When I go into the expression builder to pull
the code, the one column has the name of the function code (mdlWorkingDays2]
and the other shows just WorkingDays2 which is the actual function in the
code.

I am defining the function in a VB module.

The module appears in MS Visual Basic under "Recruitment Database -
[mdlWorkingDays2(code)]." Should it be showing up under something else?

....a couple of ideas...

The control source should start with an =
Where are you defining the function?

Well, I tried them, and I am still getting the same #Name$ issue. Any more
ideas?

Thanks to everyone for your suggestions. I will try them out and let you know
what happens. Thanks, Barb

:

Rename your module.

The module cannot have the same name as any functions or subs in the
database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am trying to calculate the number of days between two dates taking into
consideration holidays and weekends. I am using the following code:

Public Function mdlWorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: mdlWorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************
I have aTable / Form called Recruitment and a Table called tblHolidays.
Within the Table/Form called Recruitment I have a StartDate field, an
EndDate
field, and a Days field. I am entering the code as a module named
mdlWorkingDays2 in "Recruitment Database [mdlWorkingDays2 (Code)].
However, I
continue to get a #Name$ error in the Days field. In the ControlSource for
my
Days field I have mdlWorkingDays2([StartDate],[EndDate]).

Any ideas what is wrong?

 

[Rob Oldfield]

The expression builder I can't comment on because I never use it. When you
say 'the code', is that the module or the function?

To try:
Use =WorkingDays2([StartDate],[EndDate])
Does =[StartDate] work?
Does =[EndDate] work?

If none of those give any clues, then please post all code that you have,
and where it is (i.e. in form code, or a stand alone module)

I have an = sign at the beginning of the control source, and it is still
coming up with the same error. When I go into the expression builder to pull
the code, the one column has the name of the function code (mdlWorkingDays2]
and the other shows just WorkingDays2 which is the actual function in the
code.

I am defining the function in a VB module.

The module appears in MS Visual Basic under "Recruitment Database -
[mdlWorkingDays2(code)]." Should it be showing up under something else?

....a couple of ideas...

The control source should start with an =
Where are you defining the function?

Well, I tried them, and I am still getting the same #Name$ issue. Any more
ideas?

:

Thanks to everyone for your suggestions. I will try them out and let

you
know

what happens. Thanks, Barb

:

Rename your module.

The module cannot have the same name as any functions or subs in the
database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am trying to calculate the number of days between two dates

taking
into

consideration holidays and weekends. I am using the following code:

Public Function mdlWorkingDays2(StartDate As Date, EndDate As

Date)
As

Integer
'....................................................................
' Name: mdlWorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for

holidays.
It

requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************
I have aTable / Form called Recruitment and a Table called tblHolidays.
Within the Table/Form called Recruitment I have a StartDate

field,
an

EndDate
field, and a Days field. I am entering the code as a module named
mdlWorkingDays2 in "Recruitment Database [mdlWorkingDays2 (Code)].
However, I
continue to get a #Name$ error in the Days field. In the ControlSource for
my
Days field I have mdlWorkingDays2([StartDate],[EndDate]).

Any ideas what is wrong?

 

[Guest]

Hurray, I got rid of the #Name$ error by changing the Control Source to read
=[WorkingDays2]([StartDate],[EndDate]). Now, however, no matter what dates I
put into the StartDate and EndDate fields, I get a result of "0.00" days. Any
ideas?

The expression builder I can't comment on because I never use it. When you
say 'the code', is that the module or the function?

To try:
Use =WorkingDays2([StartDate],[EndDate])
Does =[StartDate] work?
Does =[EndDate] work?

If none of those give any clues, then please post all code that you have,
and where it is (i.e. in form code, or a stand alone module)

I have an = sign at the beginning of the control source, and it is still
coming up with the same error. When I go into the expression builder to pull
the code, the one column has the name of the function code (mdlWorkingDays2]
and the other shows just WorkingDays2 which is the actual function in the
code.

I am defining the function in a VB module.

The module appears in MS Visual Basic under "Recruitment Database -
[mdlWorkingDays2(code)]." Should it be showing up under something else?

....a couple of ideas...

The control source should start with an =
Where are you defining the function?



Well, I tried them, and I am still getting the same #Name$ issue. Any more
ideas?

:

Thanks to everyone for your suggestions. I will try them out and let you
know
what happens. Thanks, Barb

:

Rename your module.

The module cannot have the same name as any functions or subs in the
database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am trying to calculate the number of days between two dates taking
into
consideration holidays and weekends. I am using the following code:

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

'....................................................................
' Name: mdlWorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between
them
' Note that this function has been modified to account for holidays.
It
requires a table
' named tblHolidays with a field named HolidayDate.

'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************
I have aTable / Form called Recruitment and a Table called
tblHolidays.
Within the Table/Form called Recruitment I have a StartDate field,
an
EndDate
field, and a Days field. I am entering the code as a module named
mdlWorkingDays2 in "Recruitment Database [mdlWorkingDays2 (Code)].
However, I
continue to get a #Name$ error in the Days field. In the
ControlSource for
my
Days field I have mdlWorkingDays2([StartDate],[EndDate]).

Any ideas what is wrong?

 

[Rob Oldfield]

Strange. There seems to be an issue about the function name. If you use
Edit, Find and tell it to search for WorkingDays in the 'Current Project'
(i.e. the entire database), and then use Find Next, does it just find a
single function? I'm just a bit concerned that you have multiple function
in there. If it just finds a single function, then could you please post
it - just want to check that nothing has changed since your earlier post.

Hurray, I got rid of the #Name$ error by changing the Control Source to read
=[WorkingDays2]([StartDate],[EndDate]). Now, however, no matter what dates I
put into the StartDate and EndDate fields, I get a result of "0.00" days. Any
ideas?

The expression builder I can't comment on because I never use it. When you
say 'the code', is that the module or the function?

To try:
Use =WorkingDays2([StartDate],[EndDate])
Does =[StartDate] work?
Does =[EndDate] work?

If none of those give any clues, then please post all code that you have,
and where it is (i.e. in form code, or a stand alone module)

I have an = sign at the beginning of the control source, and it is still
coming up with the same error. When I go into the expression builder

to
pull

the code, the one column has the name of the function code (mdlWorkingDays2]
and the other shows just WorkingDays2 which is the actual function in the
code.

I am defining the function in a VB module.

The module appears in MS Visual Basic under "Recruitment Database -
[mdlWorkingDays2(code)]." Should it be showing up under something else?


:

....a couple of ideas...

The control source should start with an =
Where are you defining the function?



Well, I tried them, and I am still getting the same #Name$ issue.

Any
more

ideas?

:

Thanks to everyone for your suggestions. I will try them out and

let
you

know
what happens. Thanks, Barb

:

Rename your module.

The module cannot have the same name as any functions or subs

in
the

database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am trying to calculate the number of days between two dates taking
into
consideration holidays and weekends. I am using the

following
code:

Public Function mdlWorkingDays2(StartDate As Date, EndDate

As
Date)

As
Integer

'....................................................................
' Name: mdlWorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between
them
' Note that this function has been modified to account for holidays.
It
requires a table
' named tblHolidays with a field named HolidayDate.

'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************
I have aTable / Form called Recruitment and a Table called
tblHolidays.
Within the Table/Form called Recruitment I have a StartDate field,
an
EndDate
field, and a Days field. I am entering the code as a module named
mdlWorkingDays2 in "Recruitment Database [mdlWorkingDays2 (Code)].
However, I
continue to get a #Name$ error in the Days field. In the
ControlSource for
my
Days field I have mdlWorkingDays2([StartDate],[EndDate]).

Any ideas what is wrong?

 

[Guest]

Ok, I checked, and I did have multiple functions running.

This is what I have it down to, but I am still getting a "0" in the
calculation.

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

Private Sub Days_BeforeUpdate(Cancel As Integer)
Use = WorkingDays2([StartDate as Date], [EndDate as Date], [Days as Integer])
End Sub

I have tried Douglas Steele's suggestion of changing the name, but that
gives me the name error back. When I remove the Sub I end up with a #Error.
When I leave it as it is above I get a "0" result. Any ideas?

Strange. There seems to be an issue about the function name. If you use
Edit, Find and tell it to search for WorkingDays in the 'Current Project'
(i.e. the entire database), and then use Find Next, does it just find a
single function? I'm just a bit concerned that you have multiple function
in there. If it just finds a single function, then could you please post
it - just want to check that nothing has changed since your earlier post.

Hurray, I got rid of the #Name$ error by changing the Control Source to read
=[WorkingDays2]([StartDate],[EndDate]). Now, however, no matter what dates I
put into the StartDate and EndDate fields, I get a result of "0.00" days. Any
ideas?

The expression builder I can't comment on because I never use it. When you
say 'the code', is that the module or the function?

To try:
Use =WorkingDays2([StartDate],[EndDate])
Does =[StartDate] work?
Does =[EndDate] work?

If none of those give any clues, then please post all code that you have,
and where it is (i.e. in form code, or a stand alone module)

I have an = sign at the beginning of the control source, and it is still
coming up with the same error. When I go into the expression builder to
pull
the code, the one column has the name of the function code
(mdlWorkingDays2]
and the other shows just WorkingDays2 which is the actual function in the
code.

I am defining the function in a VB module.

The module appears in MS Visual Basic under "Recruitment Database -
[mdlWorkingDays2(code)]." Should it be showing up under something else?


:

....a couple of ideas...

The control source should start with an =
Where are you defining the function?



Well, I tried them, and I am still getting the same #Name$ issue. Any
more
ideas?

:

Thanks to everyone for your suggestions. I will try them out and let
you
know
what happens. Thanks, Barb

:

Rename your module.

The module cannot have the same name as any functions or subs in
the
database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am trying to calculate the number of days between two dates
taking
into
consideration holidays and weekends. I am using the following
code:

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

'....................................................................
' Name: mdlWorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between
them
' Note that this function has been modified to account for
holidays.
It
requires a table
' named tblHolidays with a field named HolidayDate.

'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************
I have aTable / Form called Recruitment and a Table called
tblHolidays.
Within the Table/Form called Recruitment I have a StartDate
field,
an
EndDate
field, and a Days field. I am entering the code as a module
named
mdlWorkingDays2 in "Recruitment Database [mdlWorkingDays2
(Code)].
However, I
continue to get a #Name$ error in the Days field. In the
ControlSource for
my
Days field I have mdlWorkingDays2([StartDate],[EndDate]).

Any ideas what is wrong?

 

[Rob Oldfield]

I think the square brackets around the function name in the control source
are causing the error... change the control source to:

=WorkingDays2([StartDate],[EndDate])

but I also don't understand why the sub is getting around that. But the sub
isn't required - lose it.

What do you get now?

Ok, I checked, and I did have multiple functions running.

This is what I have it down to, but I am still getting a "0" in the
calculation.

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

Private Sub Days_BeforeUpdate(Cancel As Integer)
Use = WorkingDays2([StartDate as Date], [EndDate as Date], [Days as Integer])
End Sub

I have tried Douglas Steele's suggestion of changing the name, but that
gives me the name error back. When I remove the Sub I end up with a #Error.
When I leave it as it is above I get a "0" result. Any ideas?

Strange. There seems to be an issue about the function name. If you use
Edit, Find and tell it to search for WorkingDays in the 'Current Project'
(i.e. the entire database), and then use Find Next, does it just find a
single function? I'm just a bit concerned that you have multiple function
in there. If it just finds a single function, then could you please post
it - just want to check that nothing has changed since your earlier post.

Hurray, I got rid of the #Name$ error by changing the Control Source

to
read

=[WorkingDays2]([StartDate],[EndDate]). Now, however, no matter what

dates
I

put into the StartDate and EndDate fields, I get a result of "0.00"

days.
Any

ideas?

:

The expression builder I can't comment on because I never use it.

When
you

say 'the code', is that the module or the function?

To try:
Use =WorkingDays2([StartDate],[EndDate])
Does =[StartDate] work?
Does =[EndDate] work?

If none of those give any clues, then please post all code that you have,
and where it is (i.e. in form code, or a stand alone module)

I have an = sign at the beginning of the control source, and it is still
coming up with the same error. When I go into the expression

builder
to

pull
the code, the one column has the name of the function code
(mdlWorkingDays2]
and the other shows just WorkingDays2 which is the actual function

in
the

code.

I am defining the function in a VB module.

The module appears in MS Visual Basic under "Recruitment Database -
[mdlWorkingDays2(code)]." Should it be showing up under something else?


:

....a couple of ideas...

The control source should start with an =
Where are you defining the function?



Well, I tried them, and I am still getting the same #Name$

issue.
Any

more
ideas?

:

Thanks to everyone for your suggestions. I will try them out

and
let

you
know
what happens. Thanks, Barb

:

Rename your module.

The module cannot have the same name as any functions or

subs
in

the
database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am trying to calculate the number of days between two dates
taking
into
consideration holidays and weekends. I am using the following
code:

Public Function mdlWorkingDays2(StartDate As Date,

EndDate
As

Date)
As
Integer

'....................................................................
' Name: mdlWorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between
them
' Note that this function has been modified to account for
holidays.
It
requires a table
' named tblHolidays with a field named HolidayDate.

'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate)

vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************
I have aTable / Form called Recruitment and a Table called
tblHolidays.
Within the Table/Form called Recruitment I have a StartDate
field,
an
EndDate
field, and a Days field. I am entering the code as a module
named
mdlWorkingDays2 in "Recruitment Database [mdlWorkingDays2
(Code)].
However, I
continue to get a #Name$ error in the Days field. In the
ControlSource for
my
Days field I have mdlWorkingDays2([StartDate],[EndDate]).

Any ideas what is wrong?

 

[Guest]

Hoooooooooooraaaaaaaaaaaay! It works! Thank you!

I think the square brackets around the function name in the control source
are causing the error... change the control source to:

=WorkingDays2([StartDate],[EndDate])

but I also don't understand why the sub is getting around that. But the sub
isn't required - lose it.

What do you get now?

Ok, I checked, and I did have multiple functions running.

This is what I have it down to, but I am still getting a "0" in the
calculation.

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

Private Sub Days_BeforeUpdate(Cancel As Integer)
Use = WorkingDays2([StartDate as Date], [EndDate as Date], [Days as Integer])
End Sub

I have tried Douglas Steele's suggestion of changing the name, but that
gives me the name error back. When I remove the Sub I end up with a #Error.
When I leave it as it is above I get a "0" result. Any ideas?

Strange. There seems to be an issue about the function name. If you use
Edit, Find and tell it to search for WorkingDays in the 'Current Project'
(i.e. the entire database), and then use Find Next, does it just find a
single function? I'm just a bit concerned that you have multiple function
in there. If it just finds a single function, then could you please post
it - just want to check that nothing has changed since your earlier post.


Hurray, I got rid of the #Name$ error by changing the Control Source to
read
=[WorkingDays2]([StartDate],[EndDate]). Now, however, no matter what dates
I
put into the StartDate and EndDate fields, I get a result of "0.00" days.
Any
ideas?

:

The expression builder I can't comment on because I never use it. When
you
say 'the code', is that the module or the function?

To try:
Use =WorkingDays2([StartDate],[EndDate])
Does =[StartDate] work?
Does =[EndDate] work?

If none of those give any clues, then please post all code that you
have,
and where it is (i.e. in form code, or a stand alone module)

I have an = sign at the beginning of the control source, and it is
still
coming up with the same error. When I go into the expression builder
to
pull
the code, the one column has the name of the function code
(mdlWorkingDays2]
and the other shows just WorkingDays2 which is the actual function in
the
code.

I am defining the function in a VB module.

The module appears in MS Visual Basic under "Recruitment Database -
[mdlWorkingDays2(code)]." Should it be showing up under something
else?


:

....a couple of ideas...

The control source should start with an =
Where are you defining the function?



Well, I tried them, and I am still getting the same #Name$ issue.
Any
more
ideas?

:

Thanks to everyone for your suggestions. I will try them out and
let
you
know
what happens. Thanks, Barb

:

Rename your module.

The module cannot have the same name as any functions or subs
in
the
database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am trying to calculate the number of days between two dates
taking
into
consideration holidays and weekends. I am using the
following
code:

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


'....................................................................
' Name: mdlWorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of
weekdays
between
them
' Note that this function has been modified to account for
holidays.
It
requires a table
' named tblHolidays with a field named HolidayDate.


'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line
above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate)

vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************
I have aTable / Form called Recruitment and a Table called
tblHolidays.
Within the Table/Form called Recruitment I have a StartDate
field,
an
EndDate
field, and a Days field. I am entering the code as a module
named
mdlWorkingDays2 in "Recruitment Database [mdlWorkingDays2
(Code)].
However, I
continue to get a #Name$ error in the Days field. In the
ControlSource for
my
Days field I have

mdlWorkingDays2([StartDate],[EndDate]).

 

[Rob Oldfield]

No problem. Glad we got it working.

Hoooooooooooraaaaaaaaaaaay! It works! Thank you!

I think the square brackets around the function name in the control source
are causing the error... change the control source to:

=WorkingDays2([StartDate],[EndDate])

but I also don't understand why the sub is getting around that. But the sub
isn't required - lose it.

What do you get now?

Ok, I checked, and I did have multiple functions running.

This is what I have it down to, but I am still getting a "0" in the
calculation.

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays

between
them

' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

Private Sub Days_BeforeUpdate(Cancel As Integer)
Use = WorkingDays2([StartDate as Date], [EndDate as Date], [Days as Integer])
End Sub

I have tried Douglas Steele's suggestion of changing the name, but that
gives me the name error back. When I remove the Sub I end up with a #Error.
When I leave it as it is above I get a "0" result. Any ideas?

:

Strange. There seems to be an issue about the function name. If

you
use

Edit, Find and tell it to search for WorkingDays in the 'Current Project'
(i.e. the entire database), and then use Find Next, does it just find a
single function? I'm just a bit concerned that you have multiple function
in there. If it just finds a single function, then could you please post
it - just want to check that nothing has changed since your earlier post.


Hurray, I got rid of the #Name$ error by changing the Control

Source
to

read
=[WorkingDays2]([StartDate],[EndDate]). Now, however, no matter

what
dates

I
put into the StartDate and EndDate fields, I get a result of

"0.00"
days.

Any
ideas?

:

The expression builder I can't comment on because I never use

it.
When

you
say 'the code', is that the module or the function?

To try:
Use =WorkingDays2([StartDate],[EndDate])
Does =[StartDate] work?
Does =[EndDate] work?

If none of those give any clues, then please post all code that you
have,
and where it is (i.e. in form code, or a stand alone module)

I have an = sign at the beginning of the control source, and it is
still
coming up with the same error. When I go into the expression builder
to
pull
the code, the one column has the name of the function code
(mdlWorkingDays2]
and the other shows just WorkingDays2 which is the actual

function
in

the
code.

I am defining the function in a VB module.

The module appears in MS Visual Basic under "Recruitment Database -
[mdlWorkingDays2(code)]." Should it be showing up under something
else?


:

....a couple of ideas...

The control source should start with an =
Where are you defining the function?



Well, I tried them, and I am still getting the same #Name$ issue.
Any
more
ideas?

:

Thanks to everyone for your suggestions. I will try them

out
and

let
you
know
what happens. Thanks, Barb

:

Rename your module.

The module cannot have the same name as any functions

or
subs

in
the
database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am trying to calculate the number of days between

two
dates

taking
into
consideration holidays and weekends. I am using the
following
code:

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


'....................................................................
' Name: mdlWorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of
weekdays
between
them
' Note that this function has been modified to

account
for

holidays.
It
requires a table
' named tblHolidays with a field named HolidayDate.


'....................................................................
On Error GoTo Err_WorkingDays2

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

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line
above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And

Weekday(StartDate)

vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************
I have aTable / Form called Recruitment and a Table called
tblHolidays.
Within the Table/Form called Recruitment I have a StartDate
field,
an
EndDate
field, and a Days field. I am entering the code as a module
named
mdlWorkingDays2 in "Recruitment Database [mdlWorkingDays2
(Code)].
However, I
continue to get a #Name$ error in the Days field. In the
ControlSource for
my
Days field I have

mdlWorkingDays2([StartDate],[EndDate]).