Excluding weekend and holidays

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanks, this works for the weekends but not for the holidays. Is that because
it is reading the date in as US format in VB? I tested from a temp form
entering 01/05/05 (which is a Sunday) and it returned 02/05/05 which is a
bank holiday here in the UK. Also, this doesn't check the holiday file for
countrycode - can this be added in to the dlookup ?

I also tried the following code which works for w/e's but not holidays:

Public Function NextWorkDay(StartDate As Date) As Date
On Error GoTo Err_NextWorkDay

Dim rst As DAO.Recordset
Dim DB As DAO.Database
Dim DateOK As Boolean

Set DB = CurrentDb
Set rst = DB.OpenRecordset("Select [HolidayDate] from tblHolidays where
[CountryCode]='A'", dbOpenSnapshot)

Do Until DateOK

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"

If Weekday(StartDate, vbMonday) < 6 Then
' Date is not a weekend
If rst.NoMatch Then
' not a holiday
DateOK = True
Else
StartDate = StartDate + 1
End If
Else
StartDate = StartDate + 1
End If
Loop

NextWorkDay = StartDate

Forms!form1![NextDate] = NextWorkDay

Any ideas on how to get this working for the holiday dates? Also, once
working how would I apply to a whole recordset? Something like:

Set rst = CurrentDb.OpenRecordset("select * from Dealer Selection GB")
Do While rst.EOF = False
rst.Edit
rst!NextAudit = Call routine to Check Date and 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

 

[Guest]

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

 

[Guest]

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