Holiday day tracking

[trevorC via AccessMonster.com]

I am trying to use the code below in my DB, modified to just check if the
date is a holiday or weekend or workday.
I am curious as to how the rst.FindFirst action is working, what tells it to
goto the next record ? i thought it was findfirst then findnext ?

But for my code i just want to check the date against a table of holiday
dates.

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

 

[Douglas J. Steele]

Nothing tells it to go to the next record.

You're calling FindFirst multiple times inside the loop, each time with a
different Where condition.

 

[John Spencer]

Could you tell us what you are attempting to do? You show us a snippet of code

If all you want to do is check one date to see if it is weekday that is not a
holiday, you can use an expression like the following to return true or false.
If it returns true then the date is a workday, if it returns false then the
date is a holiday or a weekend date (Sat or Sun) or it is both.

DCount("*","tblHolidays","[Holiday Date]=" &
Format([InputDate],"\#yyyy-mm-dd\#") = 0
AND WeekDay([InputDate],2)<6

That is not the most efficient way if you are attempting to count workdays
between two dates. For calculating workdays between two dates or finding a
date that is n workdays from another date, my personal preference is to have a
utility calendar table with all the dates for an extended period of time.
Then I flag the dates that are holidays (and I also pre-generate the weekday
values to make things quicker and generally easier to write queries).

Then I could write a simple query or expression.
IF DCount("*","tblCalendar","HolidayName Is Null AND TheDate Not In (1,7) AND
The Date=" & Format([InputDate],"\#yyyy-mm-dd\#") = 1 THEN
'WorkDay
Else
'Holiday
End If

OR to get the number of workdays between two dates
DCount("*","tblCalendar","HolidayName Is Null AND TheDate Not In (1,7) AND
TheDate>=" & Format([StartDate],"\#yyyy-mm-dd\#" & " AND TheDate<=" &
Format([EndDate],"\#yyyy-mm-dd\#")


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County