Pm

[MMcCarthy]

Hi mmccarthy,
That's OK - Boys are Boys. I work with 4 guys and I know how it is.
How do you suggest weekends are being handled by this code? Further more, assuming we can hard code the holidays for, see six month. Could we then handle these days?

Helen

This procedure basically adds the 7 days one at a time. If it reaches a saturday it jumps to the following Monday. Test it out with several dates it works.

If you hard code the holidays into for example a table list of the dates. You could check if at any step in the procedure the date was in the holiday list and move on a day if it is.

Mary

[NeoPa]

Helen,

I know I'm only a boy but I did post an answer to your simplified problem earlier (look right up near the top).
As for holidays for a specified time period - Yes, it can be done but not too simply.
You would need a table of holiday dates (which you would always have to assume contained all the holiday dates for the period you needed).
You would then need to process through each date using code like MMcCarthy's (One of the other boys - Mary) but adding a DLookUp to ensure it's not just not a weekend, but also not found in the Holiday Dates table.
If you have any difficulty, then let us know and I'm sure we can go into more detail for you.
...And no I'm not really offended - just more boyish banter I'm afraid :)

[NeoPa]

Mary has an irritating habit of posting in perfectly good answers to a thread while I'm still busy cogitating on the problem :(.
(and working of course to be fair).

[MMcCarthy]

Mary has an irritating habit of posting in perfectly good answers to a thread while I'm still busy cogitating on the problem :(.
(and working of course to be fair).

Are you trying to hint that I wasn't working Adrian.

Mary

[NeoPa]

I was hoping you wouldn't notice that :blush:

-Adrian.

[okosh]

This procedure basically adds the 7 days one at a time. If it reaches a saturday it jumps to the following Monday. Test it out with several dates it works.

If you hard code the holidays into for example a table list of the dates. You could check if at any step in the procedure the date was in the holiday list and move on a day if it is.

Mary

Sorry Adrian. Sorry Mary. Please forgive me for assuming everyone on this site was a guy.
Ok, Mary regarding your code. Thank you very much. It worked just fine. Let’s say that next Wednesday (Nov 22) is a holiday? How would you implement it in your code?

Helen.

[MMcCarthy]

Sorry Adrian. Sorry Mary. Please forgive me for assuming everyone on this site was a guy.
Ok, Mary regarding your code. Thank you very much. It worked just fine. Let’s say that next Wednesday (Nov 22) is a holiday? How would you implement it in your code?

Helen.

Create a new table tblHolidays with one field HolidayDate. Add 22 November as the first record to test it.

 
Private Sub OrderDate_AfterUpdate()
Dim tempDate As Date
Dim iCount As Integer
Dim i As Integer
 
  iCount = 0
	  tempDate = Me.OrderDate
	  Do Until iCount = 7
			tempDate = tempDate + 1
 
Check_Holidays:
 
	' check if the date is on the holiday table
	If Not IsNull(DLookup("HolidayDate","tblHolidays","HolidayDate=" & tempDate)) Then
	  tempDate = tempDate + 1
	  GoTo Check_Holidays ' check again on the new date
	End If
	If Weekday(tempDate) = 7 Then
	   tempDate = tempDate + 2
	End If
	iCount = iCount + 1
  Loop
		
	  Me.NewDate = tempDate
	
End Sub

[NeoPa]

Gender favouritism huh.
I answer that question in my earlier post but oh no, you won't accept it unless it comes from Mary.
:Sulking again:

Look - now I can't stop kidding around.
Seriously Helen, I hope that helps.
You must remember though, that this type of solution does depend on the holidays table being kept properly up-to-date.

[Killer42]

One thing I would suggest is that you create a separate (and globally available) function something like
Public Function IsHoliday(pDate As Date) As Boolean
and do the holiday check in there. That way, the check is always available if you need it again, and it won't matter if you change the way you store or retrieve determine them.

Depending on all sort of things, it might even be worth loading up an array of holidays at startup or something, rather than hitting the date table each time. Might not, too. :) Either way, if you change your mind it will be nice to have the logic neatly encapsulated.

[okosh]

Create a new table tblHolidays with one field HolidayDate. Add 22 November as the first record to test it.

 
Private Sub OrderDate_AfterUpdate()
Dim tempDate As Date
Dim iCount As Integer
Dim i As Integer
 
  iCount = 0
	  tempDate = Me.OrderDate
	  Do Until iCount = 7
			tempDate = tempDate + 1
 
Check_Holidays:
 
	' check if the date is on the holiday table
	If Not IsNull(DLookup("HolidayDate","tblHolidays","HolidayDate=" & tempDate)) Then
	  tempDate = tempDate + 1
	  GoTo Check_Holidays ' check again on the new date
	End If
	If Weekday(tempDate) = 7 Then
	   tempDate = tempDate + 2
	End If
	iCount = iCount + 1
  Loop
		
	  Me.NewDate = tempDate
	
End Sub

Hi Mary,
Though the code runs all right, I am not getting the right result. I crated a column called HolidayDate in a table called tblHolidays. In that table I have Nov 23, Nov 24 as holiday. So now for date Nov 17 I should see Nov 30 as 7 day target, but it shows Nov 28.
Please help,

Helen

[MMcCarthy]

Hi Mary,
Though the code runs all right, I am not getting the right result. I crated a column called HolidayDate in a table called tblHolidays. In that table I have Nov 23, Nov 24 as holiday. So now for date Nov 17 I should see Nov 30 as 7 day target, but it shows Nov 28.
Please help,

Helen

Helen

Just posting the solution for future posters:

All dates need to be enclosed with # characters when being used in code.

 
Private Sub OrderDate_AfterUpdate()
Dim tempDate As Date
Dim iCount As Integer
Dim i As Integer
 
  iCount = 0
	  tempDate = Me.OrderDate
	  Do Until iCount = 7
			tempDate = tempDate + 1
 
Check_Holidays:
 
	' check if the date is on the holiday table
	If Not IsNull(DLookup("HolidayDate","tblHolidays","HolidayDate=#" & tempDate & "#")) Then
	  tempDate = tempDate + 1
	  GoTo Check_Holidays ' check again on the new date
	End If
	If Weekday(tempDate) = 7 Then
	   tempDate = tempDate + 2
	End If
	iCount = iCount + 1
  Loop
		
	  Me.NewDate = tempDate
	
End Sub