Skipping Weekends & holidays in VBA Code

[Secret Squirrel]

I'm using the following code to create new records for each day including the
starting date and ending date based on two controls on my form, txtVacStart &
txtVacEnd.
It creates new records and enters each of the dates into the field
"RegularDate". How can I have it automatically skip over weekends and
holidays?

Dim dtVacDay As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblVacationTime", dbOpenTable,
dbAppendOnly)

For dtVacDay = Me.txtVacStart To Me.txtVacEnd
rs.AddNew
rs.Fields("EmployeeName") = Me.EmployeeName
rs.Fields("RegularDate") = dtVacDay
rs.Update
Next dtVacDay

rs.Close
Set rs = Nothing

 

[Douglas J. Steele]

To ignore Saturday and Sunday:


Do you have a table of holidays that you want ignored?

 

[Douglas J. Steele]

Oops, somehow that got sent!

To ignore Saturday and Sunday, use

For dtVacDay = Me.txtVacStart To Me.txtVacEnd
If WeekDay(dtVacDay) <> vbSaturday And _
WeekDay(dtVacDay) <> vbSunday Then
rs.AddNew
rs.Fields("EmployeeName") = Me.EmployeeName
rs.Fields("RegularDate") = dtVacDay
rs.Update
End If
Next dtVacDay

If you've got a Holiday table of dates to ignore, make that

For dtVacDay = Me.txtVacStart To Me.txtVacEnd
If WeekDay(dtVacDay) <> vbSaturday And _
WeekDay(dtVacDay) <> vbSunday And _
IsNull(DLookup("HolidayDate", "Holidays", "HolidayDate = " & _
Format(dtVacDay, "\#yyyy\-mm\-dd\#"))) = False Then
rs.AddNew
rs.Fields("EmployeeName") = Me.EmployeeName
rs.Fields("RegularDate") = dtVacDay
rs.Update
End If
Next dtVacDay

 

[Secret Squirrel]

Yes I do have a table with a list of holidays:

HolidayID - PK
Holiday - Date/Time

 

[Secret Squirrel]

Question for you: Everything works fine but since the form is bound to the
vacation table when I run this code it adds the records correctly but it
always leaves the current record blank. I guess that's since it's adding new
records. How do I get it to use the current record as the first record? I
have my form set to open to a new record. So when I start typing it
automatically creates a new record, then when I fire the code it adds new
records but then leaves the original record blank. Is there an easy way to
correct this?

 

[Albert D. Kallal]

before you run your code, perahps you force a disk write of the current
reocrd??

if me.Dirty = true then
me.Dirty = false
end if
.....you code goes here.....

 

[Secret Squirrel]

Got it, thanks!

 

[Secret Squirrel]

Hi Doug,

I was just testing out your code and it doesn't seem to be working when I
test it using the code with the holiday portion. It's creating the records
but the "RegularDate" field is blank in the table. When I test it without the
holiday code it works fine. Here's what I have as my code. Did I miss
something? My holiday table is called "tblHolidays" and the field where the
holiday dates are kept is called "HolidayDate".

Dim dtVacDay As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblVacationTime1", dbOpenTable,
dbAppendOnly)

For dtVacDay = Me.txtVacStart To Me.txtVacEnd
If Weekday(dtVacDay) <> vbSaturday And _
Weekday(dtVacDay) <> vbSunday And _
IsNull(DLookup("HolidayDate", "tblHolidays", "HolidayDate = " & _
Format(dtVacDay, "\#yyyy\-mm\-dd\#"))) = False Then
rs.AddNew
rs.Fields("EmployeeName") = Me.EmployeeName
rs.Fields("RegularDate") = dtVacDay
rs.Update
End If
Next dtVacDay



rs.Close
Set rs = Nothing

 

[Secret Squirrel]

I ran another test and it seems to be putting the holiday date as a new
record in my table instead of the business days around the holiday. Is the
code reversed by chance?

 

[Secret Squirrel]

Figured it out. The "IsNull" portion of the code should be "Not IsNull". It
seems to work that way. Now it skips the holidays.

 

[Secret Squirrel]

Hi Albert,

Well I thought it was working but for some reason it's not. Here's what I
have for my code. Do you see anything wrong with it? It's still creating a
blank record and then creating new records for all my dates within the range.


If Me.Dirty = True Then
Me.Dirty = False
End If

Dim dtVacDay As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblVacationTime1", dbOpenTable,
dbAppendOnly)

For dtVacDay = Me.txtVacStart To Me.txtVacEnd
If Weekday(dtVacDay) <> vbSaturday And _
Weekday(dtVacDay) <> vbSunday And _
Not IsNull(DLookup("HolidayDate", "tblHolidays", "HolidayDate = " & _
Format(dtVacDay, "\#yyyy\-mm\-dd\#"))) = False Then
rs.AddNew
rs.Fields("EmployeeName") = Me.EmployeeName
rs.Fields("RegularDate") = dtVacDay
rs.Update
End If
Next dtVacDay

rs.Close
Set rs = Nothing