Hello All
I have utilized the excellent examples from Duane Hookom recurring
calendar mdb and have a question about modding for holidays.

http://www.rogersaccesslibrary.com/f...-c9362z78.html

If I understand correctly, the best way to remove holidays from a
schedule is to have a holiday table, create the schedule records and
skip anything that matches from the holiday table.

I have tried to add code from Duane's and have not been able to add to
the loop

Set db = CurrentDb
If Me.grpRepeats = 2 Then 'need to loop through dates
For datThis = Me.txtStartDate To Me.txtEndDate
intDIM = GetDIM(datThis) 'day in month
intDOW = Weekday(datThis) ' day of week

'I have tried to add a DLookup something like
' IsNull(DLookup("[HolidayDate]", "holidays", _
' "[HolidayDate] = #" & tscDate & "#")) Then
' Days = Days - 1


If Me("chkDay" & intDIM & intDOW) = True Or _
Me("chkDay0" & intDOW) = True Then
strSQL = "INSERT INTO tblTempSchedDates (" & _
"tscDate, tscActID, tscLocID, " & _
"tscStartTime, tscEndTime, tscNotes ) " & _
"Values(#" & datThis & "#," & lngActID & ", " & _
lngLocID & ", #" & Me.txtStartTime & "#, #" & _
Me.txtEndTime & "#," & _
IIf(IsNull(varNotes), "Null", """" & varNotes &
"""") & ")"
db.Execute strSQL, dbFailOnError
End If
Next


I have tried several different ways to incorporate into the loop to
prevent the inclusion of the holiday records but no cigar.

Any help/suggestions/examples would be appreciated.
Many thanks
John

"flymo" <fly_mo (AT) hotmail (DOT) com> wrote

As posted by Bob Barrows on 6/7/11 Look at the examples in the following
page
http://access.mvps.org/access/datetime/date0006.htm
Specifically look at the second method since it takes holidays into account.

or as posted by me a day later, a 1 line approach using a holiday table

WeekDayCount = DateDiff("d", dtStartDate, dtEndDate) + 1 - (DateDiff("ww",
dtStartDate, dtEndDate) * 2) - IIf(WeekDay(dtStartDate) = vbSunday, 1, 0) -
IIf(WeekDay(dtEndDate) = vbSaturday, 1, 0) -
dcount("[dtDate]","tblHolidays","[dtDate] between #" & dtStartDate & "# and
#" & dtEndDate & "#")

On Jun 10, 2:55*pm, "ron paii" <n... (AT) nospam (DOT) com> wrote:
Hello Ron,
thanks for posting the links and your example. I have the same
problem as the poster from the other thread (which I hadn't seen)
I have modified the code from Duane to work with my own tables/forms
etc and re-written parts of the functions, but I am having trouble
inserting the dlookup code into the loop as I don't fully understand
how to add that to the existing code/loop so that the dates are
excluded (if it's a date within the holiday table, don't create the
record, skip to the next)
It seems to be a similar idea to your own.

I have also created a query to pull those dates from the schedule
table and try to delete them, this also seems to fail
Regards
John

"flymo" <fly_mo (AT) hotmail (DOT) com> wrote

from holiday

If Me("chkDay" & intDIM & intDOW) = True Or Me("chkDay0" & intDOW) = True
Then

'The following will check if the date is between Monday and Friday and is
not a holiday
'Note: I am using datThis for the date, where your holiday check used
tscDate

if (weekday(datThis) <> vbSunday and _
weekday(datThis) <> vbSaturday and _
isnull(Dlookup("[HolidayDate]", "Holidays", "[HolidayDate] = #" &
(datThis) & "#")) Then

strSQL = "INSERT INTO tblTempSchedDates (tscDate, tscActID,
tscLocID, tscStartTime, tscEndTime, tscNotes ) " & _
"Values(#" & datThis & "#," & lngActID & ", lngLocID & ",
#" & Me.txtStartTime & "#, #" & _
Me.txtEndTime & "#," IIf(IsNull(varNotes), "Null",
"""" & varNotes & """") & ")"
db.Execute strSQL, dbFailOnError
End If
End If

On Jun 10, 4:19*pm, "ron paii" <n... (AT) nospam (DOT) com> wrote:
Thanks Ron,
That makes more sense than the way I was trying to make it work. The
code you mentioned loops through a series of checkboxes that pick out
the Day of Month and Day of Week so I will play around with this.

Really appreciated.
John

On Jun 10, 4:41*pm, flymo <fly... (AT) hotmail (DOT) com> wrote:
Thanks Ron
This worked like a charm, I need to adjust the And/Or to incorporate
the previous statement and it takes all weekends & holidays listed in
the table.

If Me.grpRepeats = 2 Then 'need to loop through dates
For datThis = Me.txtStartDate To Me.txtEndDate
intDIM = GetDIM(datThis)
intDOW = Weekday(datThis)
If Me("chkDay" & intDIM & intDOW) = True Or _
Me("chkDay0" & intDOW) = True And _
(Weekday(datThis) <> vbSunday And _
Weekday(datThis) <> vbSaturday And _
IsNull(DLookup("[HolidayDate]", "holidays", "[HolidayDate] = #"
& (datThis) & "#"))) Then

strSQL = "INSERT INTO tblTempSchedDates (" & _
"tscDate, " & _
"tscStartTime, tscEndTime, faculty_ID, f_clinicID,
discID, sessionID, " & _
"acc_yearID, acc_termID, tscNotes ) " & _
"Values(#" & datThis & "#, #" & Me.txtStartTime &
"#, #" & _
Me.txtEndTime & "#," & lngFacID & "," &
lngF_clinicID & "," & lngDisciplineID & ", " & _
lngSessionID & "," & lngAccYearID & "," &
lngAccTermID & "," & _
IIf(IsNull(varNotes), "Null", """" & varNotes &
"""") & ")"
' Debug.Print strSQL
' Stop

db.Execute strSQL, dbFailOnError
End If


Regards
John

"flymo" <fly_mo (AT) hotmail (DOT) com> wrote

Glad you got it to work.

PS: if you have a performance problem, put the dlookup in a separate IF
inside the 1st. As it is now it will be run whenever the IF statement is
executed.

On Jun 13, 2:28*pm, "ron paii" <n... (AT) nospam (DOT) com> wrote:
Excellent suggestion Ron, I will have a run at that shortly
Thanks
John