Calculating Dates

  • Thread starter Thread starter scott04
  • Start date Start date
S

scott04

Hi everyone I am trying to calculate a date difference excluding holidays. I
have a tbl in the database name tblholidays with a HolidayDate Field in the
table. I have a textbox in my report with the control source
=(WorkingDays2([Sdate],[Completed_by_date]))
My module is the following
Public Function Workingdays2(SDate As Date, Completed_by_date As Date) As
Integer
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)
intCount = 0
Do While SDate <= Completed_by_date
rst.FindFirst "[HolidayDate] = #" & SDate & "#"
If Weekday(SDate) <> vbSunday And Weekday(SDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If
SDate = SDate + 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

Can someone please see if i have done something wrong as it is not looking
at the dates i input in my tblHolidays table?? Example i have 12/31/2008 as
SDate and 1/13/2009 as Completed_by_date but result = 10 when i have
1/1/2009 as a holiday. Thanks.
 
Hi Scott,

Is the following really all on one line?

If rst.NoMatch Then intCount = intCount + 1

If so move the part after the Then to a new line. Also, I would move
the FindFirst inside of the If Weekday... section. No reason to do it if the
date is a weekend.

Also, it helps to add this line at the top of your module with any
other Option statements:

Option Explicit

It will force the declaration of all variables. So if you try to use
one that is not declared, it will complain. Helpful for finding mistyped
variable names. Also you should do a compile of the code (Debug menu,
Compile Database) to see if there are any syntax errors.

Finally, if it still does not work, put a break point in the code, say
in the intCount = 0 line, and run the process. Click in the left margin to
set a break point. A large dot will apear. When it breaks, use Shift-F8 to
step from line to line. See what happens. You can hover your cursor over
the variables to see their current values.

Clifford Bass
 
Clifford,

Thanks for assistance...it looks like when i changed this line it worked:
rst.FindFirst "[HolidayDate] = #" & SDate & "#"
changed to:
rst.FindFirst "[HolidayDate] > #" & SDate & "#"

Clifford Bass said:
Hi Scott,

Is the following really all on one line?

If rst.NoMatch Then intCount = intCount + 1

If so move the part after the Then to a new line. Also, I would move
the FindFirst inside of the If Weekday... section. No reason to do it if the
date is a weekend.

Also, it helps to add this line at the top of your module with any
other Option statements:

Option Explicit

It will force the declaration of all variables. So if you try to use
one that is not declared, it will complain. Helpful for finding mistyped
variable names. Also you should do a compile of the code (Debug menu,
Compile Database) to see if there are any syntax errors.

Finally, if it still does not work, put a break point in the code, say
in the intCount = 0 line, and run the process. Click in the left margin to
set a break point. A large dot will apear. When it breaks, use Shift-F8 to
step from line to line. See what happens. You can hover your cursor over
the variables to see their current values.

Clifford Bass

scott04 said:
Hi everyone I am trying to calculate a date difference excluding holidays. I
have a tbl in the database name tblholidays with a HolidayDate Field in the
table. I have a textbox in my report with the control source
=(WorkingDays2([Sdate],[Completed_by_date]))
My module is the following
Public Function Workingdays2(SDate As Date, Completed_by_date As Date) As
Integer
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)
intCount = 0
Do While SDate <= Completed_by_date
rst.FindFirst "[HolidayDate] = #" & SDate & "#"
If Weekday(SDate) <> vbSunday And Weekday(SDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If
SDate = SDate + 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

Can someone please see if i have done something wrong as it is not looking
at the dates i input in my tblHolidays table?? Example i have 12/31/2008 as
SDate and 1/13/2009 as Completed_by_date but result = 10 when i have
1/1/2009 as a holiday. Thanks.
Click to expand...
Click to expand...
 
Hi Scott,

That is odd. Because your original code works for me; returns 9. And
changing it to > does not work; returns 0. Do you have holidays after
1/13/09 such as 12/25/09 in your table? If not add one or more and test
again.

Clifford Bass
 
Back
Top