K
Kirk Justus via AccessMonster.com
I'll begin with the statement that, "I am not a programmer." However, I do
work with Access and Excel daily, and have written some--albeit very
simplistic--VB modules.
Objective: My objective is to create a VB function module that I can use
to calculate the number of Working Days between two dates--total number of
days, less weekends and holidays.
Steps so far: Elsewhere on this site, I found referneces to other sites
were "A Couple of way to do it" could be found. The code I have so far is
below.
The problem is, I guess, that it was written for an earlier version of VB
and either the statements are no longer valid or the expected syntax is
different. I am using Access XP and it appears to be VB6.3.
The error occurs after I enter the function and run the query. But only
when I scroll over to the column where the function is used. The error is
"Compile Error - User-Defined type not defined" and it highlights the
everything in the after the "Dim" statement in this line: "Dim rst As
DAO.Recordset"
I would greatly appreciate any help someone could render.
--Kirk Justus
<pre>
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
</pre>
work with Access and Excel daily, and have written some--albeit very
simplistic--VB modules.
Objective: My objective is to create a VB function module that I can use
to calculate the number of Working Days between two dates--total number of
days, less weekends and holidays.
Steps so far: Elsewhere on this site, I found referneces to other sites
were "A Couple of way to do it" could be found. The code I have so far is
below.
The problem is, I guess, that it was written for an earlier version of VB
and either the statements are no longer valid or the expected syntax is
different. I am using Access XP and it appears to be VB6.3.
The error occurs after I enter the function and run the query. But only
when I scroll over to the column where the function is used. The error is
"Compile Error - User-Defined type not defined" and it highlights the
everything in the after the "Dim" statement in this line: "Dim rst As
DAO.Recordset"
I would greatly appreciate any help someone could render.
--Kirk Justus
<pre>
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
</pre>