G
Guest
Hello,
I've been given a project at work which involves using forms and tables in
Access.
I've got the possibility of two different dates being entered into a field.
I need to calculate a certain amount of working days depending on which field
the user completes.
I've seen this code;
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
'*********** Code End **************
But I don't know how to incorporate this code into the other bits of code
that I've already got - I'm quite new to all this coding! I’ve created the
tblholiday and added the holidays to the table.
The input fields i have are called - [date_complaint_received] and
[date_complaint_agreed]. I want to add 10 working days (including holidays)
onto which ever field is completed.
Any help will be gratefully received!!
Many Thanks - WOODA
I've been given a project at work which involves using forms and tables in
Access.
I've got the possibility of two different dates being entered into a field.
I need to calculate a certain amount of working days depending on which field
the user completes.
I've seen this code;
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
'*********** Code End **************
But I don't know how to incorporate this code into the other bits of code
that I've already got - I'm quite new to all this coding! I’ve created the
tblholiday and added the holidays to the table.
The input fields i have are called - [date_complaint_received] and
[date_complaint_agreed]. I want to add 10 working days (including holidays)
onto which ever field is completed.
Any help will be gratefully received!!
Many Thanks - WOODA