By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,383 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

Pm

P: 7
Hi All,
I need to calculate a date filed in my Access application called ď7 Day TargetedĒ. This field will take a date value from a date text (say, 07/14/2006, entered by user) and returns a date (say, 07/24/2006) 7days after calculating the number of business days. Oh, I also have to take holidays into consideration.

Helen

Thank you all

okosh "AT" provide "DOT" net

(changed by moderator - full email addresses get picked up by trawling engines for spamming)
Nov 10 '06 #1
Share this Question
Share on Google+
25 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi

Cvdate([YourField])+7 isn't it enough?
Nov 11 '06 #2

P: 7
Hi

Cvdate([YourField])+7 isn't it enough?
Hi PEB,
Thank you for the response. +7 would be a constant, however what if in that 7 days there are some holidays, say 3 days. So now the 7 days will increase to 10 days. I have handled this issue in Excel but Access is a different game. Nevertheless I will try your suggestion and let you know how it works.
Any idea why I can't see my posting?

Helen
Nov 11 '06 #3

NeoPa
Expert Mod 15k+
P: 31,492
Sometimes the posted text 'disappears' on these forums.
I think it's a bug in the software.
If that ever happens, you can see it if you select it (drag over with the mouse).
Naff I know but at least you can read it.
Alternatively, Copy / Paste it elsewhere.

Dates :
Moving by weekday is a little more difficult.
DateAdd("w",7,YourDate) should work (but doesn't seem to) for weekdays so I wrote my own.
Expand|Select|Wrap|Line Numbers
  1. 'MoveWD moves datThis on by the intInc weekdays.
  2. Public Function MoveWD(datThis As Date, intInc As Integer) As Date
  3.     MoveWD = datThis
  4.     For intInc = intInc To Sgn(intInc) Step -Sgn(intInc)
  5.         MoveWD = MoveWD + Sgn(intInc)
  6.         Do While (WeekDay(MoveWD) Mod 7) < 2
  7.             MoveWD = MoveWD + Sgn(intInc)
  8.         Loop
  9.     Next intInc
  10. End Function
There is nothing that works automatically for holidays though.
Nov 11 '06 #4

PEB
Expert 100+
P: 1,418
PEB
And 3 days holiday...

In Bg here there isn't such a holidays.... I'm curious where is it?

Extracting the weekends can help for Saturday and Sunday...

But for the others... Hein... I want more holidays...
Nov 12 '06 #5

NeoPa
Expert Mod 15k+
P: 31,492
lol - 3 days holiday if you include weekends ;)
But I want more holidays too :(
Christmas next month - The season to be jolly - and have loads of holidays - yay.
Nov 12 '06 #6

PEB
Expert 100+
P: 1,418
PEB
But my computer, the poor it wonna't be in holiday... And it shouldn't count this holiday ;)

So only the weekends are possible

And the musulmans on Cristmas don't have holiday maybe??? Theirs computers what they say about he holidays in those cases??? :(
Nov 12 '06 #7

NeoPa
Expert Mod 15k+
P: 31,492
Even in the Christian Calendar holidays vary their dates from year to year (EG Easter).
This is why handling holidays has been a big problem in code since as far back as I can remember (late seventies).

Perhaps we could just say that all Mondays and Fridays are holidays from now on... Why should the working week be longer than the weekend anyway?
Nov 12 '06 #8

Expert 5K+
P: 8,434
Even in the Christian Calendar holidays vary their dates from year to year (EG Easter).
This is why handling holidays has been a big problem in code since as far back as I can remember (late seventies).

Perhaps we could just say that all Mondays and Fridays are holidays from now on... Why should the working week be longer than the weekend anyway?
I think the "some holidays, say 3 days" that okosh referred to were probably all just weekends. Consider, if your starting date is a Sunday, "7 days after" would be the following Sunday. So you would have a Saturday and two Sundays.

Any further holidays would obviously have to be configurable for the particular site.
Nov 12 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
lol - 3 days holiday if you include weekends ;)
But I want more holidays too :(
Christmas next month - The season to be jolly - and have loads of holidays - yay.
I worked so hard coming up to xmas last year I was sick for the whole time. Looks like I'm heading in the same direction this year.



Enough scrooge for now.



Mary
Nov 12 '06 #10

P: 7
Ok All,
So, letís forget the holidays and focus only on counting M-F (workdays). Say if the user enters an order on Tuesday, I need to take that value and show a date for 7 working days later, which should give me the Thursday of the following week.
There Ė simplified.

Helen
Nov 12 '06 #11

Expert 5K+
P: 8,434
Ok All,
So, letís forget the holidays and focus only on counting M-F (workdays). Say if the user enters an order on Tuesday, I need to take that value and show a date for 7 working days later, which should give me the Thursday of the following week.
There Ė simplified.

Helen
Well, I guess NeoPa's routine should do that.

As for working yourself sick, hm... bad idea.

I found I was always working up until about Christmas Eve, then rushing around like a headless chook. So last year I arranged some leave starting a couple of weeks before Christmas. Wow, what a difference! I had time to do Christmas shopping, organise a family gathering at my house to take the load off my ageing parents, gardening, etc etc.

My advice - make time to take a holiday this year.

Sorry, getting a bit off track there. :)
Nov 13 '06 #12

Expert 5K+
P: 8,434
Ok All,
So, letís forget the holidays and focus only on counting M-F (workdays). Say if the user enters an order on Tuesday, I need to take that value and show a date for 7 working days later, which should give me the Thursday of the following week.
There Ė simplified.

Helen
Sorry, one more question just came to mind. Is it possible (not likely, but possible) that the user could enter the order on a non-workday (Saturday, for example)? If it's possible, it has to be allowed for. People defining requirements for a system far too often say things like "no, that won't happen". There's a huge difference between won't and can't.
Nov 13 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
Ok All,
So, letís forget the holidays and focus only on counting M-F (workdays). Say if the user enters an order on Tuesday, I need to take that value and show a date for 7 working days later, which should give me the Thursday of the following week.
There Ė simplified.

Helen
Hey Helen

We weren't ignoring you, but simply bantering while waiting on you to respond. We're a friendly interactive community and I'm sorry if we get carried away sometimes.

To answer your question:

Assuming you have two fields [OrderDate] and [NewDate].

In the After Update event of [OrderDate] put the following code.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub OrderDate_AfterUpdate()
  3. Dim tempDate As Date
  4. Dim iCount As Integer
  5. Dim i As Integer
  6.     iCount = 0
  7.     tempDate = Me.OrderDate
  8.     Do Until iCount = 7
  9.         tempDate = tempDate + 1
  10.         If Weekday(tempDate) = 7 Then
  11.             tempDate = tempDate + 2
  12.         End If
  13.         iCount = iCount + 1
  14.     Loop
  15.  
  16.     Me.NewDate = tempDate
  17.  
  18. End Sub
  19.  
  20.  
Nov 13 '06 #14

P: 7
Hey Helen

We weren't ignoring you, but simply bantering while waiting on you to respond. We're a friendly interactive community and I'm sorry if we get carried away sometimes.

To answer your question:

Assuming you have two fields [OrderDate] and [NewDate].

In the After Update event of [OrderDate] put the following code.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub OrderDate_AfterUpdate()
  3. Dim tempDate As Date
  4. Dim iCount As Integer
  5. Dim i As Integer
  6.     iCount = 0
  7.     tempDate = Me.OrderDate
  8.     Do Until iCount = 7
  9.         tempDate = tempDate + 1
  10.         If Weekday(tempDate) = 7 Then
  11.             tempDate = tempDate + 2
  12.         End If
  13.         iCount = iCount + 1
  14.     Loop
  15.  
  16.     Me.NewDate = tempDate
  17.  
  18. End Sub
  19.  
  20.  
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
Nov 13 '06 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 13 '06 #16

NeoPa
Expert Mod 15k+
P: 31,492
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 :)
Nov 13 '06 #17

NeoPa
Expert Mod 15k+
P: 31,492
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).
Nov 13 '06 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 13 '06 #19

NeoPa
Expert Mod 15k+
P: 31,492
I was hoping you wouldn't notice that :blush:

-Adrian.
Nov 13 '06 #20

P: 7
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.
Nov 13 '06 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub OrderDate_AfterUpdate()
  3. Dim tempDate As Date
  4. Dim iCount As Integer
  5. Dim i As Integer
  6.  
  7.   iCount = 0
  8.       tempDate = Me.OrderDate
  9.       Do Until iCount = 7
  10.             tempDate = tempDate + 1
  11.  
  12. Check_Holidays:
  13.  
  14.     ' check if the date is on the holiday table
  15.     If Not IsNull(DLookup("HolidayDate","tblHolidays","HolidayDate=" & tempDate)) Then
  16.       tempDate = tempDate + 1
  17.       GoTo Check_Holidays ' check again on the new date
  18.     End If
  19.     If Weekday(tempDate) = 7 Then
  20.        tempDate = tempDate + 2
  21.     End If
  22.     iCount = iCount + 1
  23.   Loop
  24.  
  25.       Me.NewDate = tempDate
  26.  
  27. End Sub
  28.  
  29.  
Nov 13 '06 #22

NeoPa
Expert Mod 15k+
P: 31,492
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.
Nov 13 '06 #23

Expert 5K+
P: 8,434
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.
Nov 14 '06 #24

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub OrderDate_AfterUpdate()
  3. Dim tempDate As Date
  4. Dim iCount As Integer
  5. Dim i As Integer
  6.  
  7.   iCount = 0
  8.       tempDate = Me.OrderDate
  9.       Do Until iCount = 7
  10.             tempDate = tempDate + 1
  11.  
  12. Check_Holidays:
  13.  
  14.     ' check if the date is on the holiday table
  15.     If Not IsNull(DLookup("HolidayDate","tblHolidays","HolidayDate=" & tempDate)) Then
  16.       tempDate = tempDate + 1
  17.       GoTo Check_Holidays ' check again on the new date
  18.     End If
  19.     If Weekday(tempDate) = 7 Then
  20.        tempDate = tempDate + 2
  21.     End If
  22.     iCount = iCount + 1
  23.   Loop
  24.  
  25.       Me.NewDate = tempDate
  26.  
  27. End Sub
  28.  
  29.  

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
Nov 16 '06 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub OrderDate_AfterUpdate()
  3. Dim tempDate As Date
  4. Dim iCount As Integer
  5. Dim i As Integer
  6.  
  7.   iCount = 0
  8.       tempDate = Me.OrderDate
  9.       Do Until iCount = 7
  10.             tempDate = tempDate + 1
  11.  
  12. Check_Holidays:
  13.  
  14.     ' check if the date is on the holiday table
  15.     If Not IsNull(DLookup("HolidayDate","tblHolidays","HolidayDate=#" & tempDate & "#")) Then
  16.       tempDate = tempDate + 1
  17.       GoTo Check_Holidays ' check again on the new date
  18.     End If
  19.     If Weekday(tempDate) = 7 Then
  20.        tempDate = tempDate + 2
  21.     End If
  22.     iCount = iCount + 1
  23.   Loop
  24.  
  25.       Me.NewDate = tempDate
  26.  
  27. End Sub
  28.  
  29.  
Nov 16 '06 #26

Post your reply

Sign in to post your reply or Sign up for a free account.