Excluding weekend and holidays

[hughess7]

I want my database to suggest the next working day to a user for an
appointment, excluding weekends and holidays. I have seen the code at
http://www.mvps.org/access/datetime/date0012.htm but not sure how to utilise
this?

I have the following:-

Tables:
tblHolidays - a holiday table which is keyed on countrycode and dates (E =
France)
Dealer Selection Fr - French data (each country has its own table)

I want to open the recordset (Dealer Selection Fr) and Update NextDate field
value to the next available working day. The system would have to exclude
weekends and any holiday dates against countrycode 'E' in tblHolidays.

The example code off the website uses an array adtmDates for the holiday
dates but not sure how I read my holiday table into this? Sorry I am a novice
when it comes to coding :-(

Thanks in advance for any help.
Sue

 

[Klatuu]

Change the names where you need to, but this will work

Function NextWorkDate(dtmToday As Date) As Date
Dim blnIsAWorkDay As Boolean

dtmToday = DateAdd("d", 1, dtmToday)
Do Until blnIsAWorkDay
If Weekday(dtmToday, vbMonday) > 5 Then
dtmToday = DateAdd("d", 1, dtmToday)
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then
dtmToday = DateAdd("d", 1, dtmToday)
Else
blnIsAWorkDay = True
End If
NextWorkDate = dtmToday
Loop

End Function

 

[hughess7]

Modified line to :
rst.FindFirst "[HolidayDate] = #" & Format$(StartDate, "mm\/dd\/yyyy")
& "#"
and holidays now work too .

Just need to know the best way to make this code run through an entire
recordset updating the Next Audit Date to a working day if it falls on a
weekend or a holiday. I expect it is something like the following but not
sure how to call the NextWorkDay function and pass the existing date in ?

Set rst = CurrentDb.OpenRecordset("select * from Dealer Selection GB")
Do While rst.EOF = False
rst.Edit
rst!NextAudit = (Call routine to Update to Next Working Day where
appropriate)
rst.Update

rst.MoveNext
Loop

rst.Close
Set rst = Nothing

Thanks in advance for any help.
Sue

Change the names where you need to, but this will work

Function NextWorkDate(dtmToday As Date) As Date
Dim blnIsAWorkDay As Boolean

dtmToday = DateAdd("d", 1, dtmToday)
Do Until blnIsAWorkDay
If Weekday(dtmToday, vbMonday) > 5 Then
dtmToday = DateAdd("d", 1, dtmToday)
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then
dtmToday = DateAdd("d", 1, dtmToday)
Else
blnIsAWorkDay = True
End If
NextWorkDate = dtmToday
Loop

End Function

I want my database to suggest the next working day to a user for an
appointment, excluding weekends and holidays. I have seen the code at
http://www.mvps.org/access/datetime/date0012.htm but not sure how to utilise
this?

I have the following:-

Tables:
tblHolidays - a holiday table which is keyed on countrycode and dates (E =
France)
Dealer Selection Fr - French data (each country has its own table)

I want to open the recordset (Dealer Selection Fr) and Update NextDate field
value to the next available working day. The system would have to exclude
weekends and any holiday dates against countrycode 'E' in tblHolidays.

The example code off the website uses an array adtmDates for the holiday
dates but not sure how I read my holiday table into this? Sorry I am a novice
when it comes to coding :-(

Thanks in advance for any help.
Sue

 

[Klatuu]

Your suggested code looks correct. Yes, it was in US date format. As to the
holidays, you will have to put in your own holidays. They would be too local
to be universal. I would bet that whereever you are, you don't celebrate San
Jancinto day.

Modified line to :
rst.FindFirst "[HolidayDate] = #" & Format$(StartDate, "mm\/dd\/yyyy")
& "#"
and holidays now work too .

Just need to know the best way to make this code run through an entire
recordset updating the Next Audit Date to a working day if it falls on a
weekend or a holiday. I expect it is something like the following but not
sure how to call the NextWorkDay function and pass the existing date in ?

Set rst = CurrentDb.OpenRecordset("select * from Dealer Selection GB")
Do While rst.EOF = False
rst.Edit
rst!NextAudit = (Call routine to Update to Next Working Day where
appropriate)
rst.Update

rst.MoveNext
Loop

rst.Close
Set rst = Nothing

Thanks in advance for any help.
Sue

Change the names where you need to, but this will work

Function NextWorkDate(dtmToday As Date) As Date
Dim blnIsAWorkDay As Boolean

dtmToday = DateAdd("d", 1, dtmToday)
Do Until blnIsAWorkDay
If Weekday(dtmToday, vbMonday) > 5 Then
dtmToday = DateAdd("d", 1, dtmToday)
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then
dtmToday = DateAdd("d", 1, dtmToday)
Else
blnIsAWorkDay = True
End If
NextWorkDate = dtmToday
Loop

End Function


:

I want my database to suggest the next working day to a user for an
appointment, excluding weekends and holidays. I have seen the code at
http://www.mvps.org/access/datetime/date0012.htm but not sure how to utilise
this?

I have the following:-

Tables:
tblHolidays - a holiday table which is keyed on countrycode and dates (E =
France)
Dealer Selection Fr - French data (each country has its own table)

I want to open the recordset (Dealer Selection Fr) and Update NextDate field
value to the next available working day. The system would have to exclude
weekends and any holiday dates against countrycode 'E' in tblHolidays.

The example code off the website uses an array adtmDates for the holiday
dates but not sure how I read my holiday table into this? Sorry I am a novice
when it comes to coding :-(

Thanks in advance for any help.
Sue