Pm

[okosh]

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)

[PEB]

Hi

Cvdate([YourField])+7 isn't it enough?

[okosh]

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

[NeoPa]

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,Y ourDate) should work (but doesn't seem to) for weekdays so I wrote my own.

'MoveWD moves datThis on by the intInc weekdays.
Public Function MoveWD(datThis As Date, intInc As Integer) As Date
    MoveWD = datThis
    For intInc = intInc To Sgn(intInc) Step -Sgn(intInc)
        MoveWD = MoveWD + Sgn(intInc)
        Do While (WeekDay(MoveWD) Mod 7) < 2
            MoveWD = MoveWD + Sgn(intInc)
        Loop
    Next intInc
End Function

There is nothing that works automatically for holidays though.

[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...

[NeoPa]

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.

[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??? :(

[NeoPa]

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?

[Killer42]

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.

[MMcCarthy]

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

[okosh]

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

[Killer42]

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. :)

[Killer42]

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.

[MMcCarthy]

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.

 
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
		If Weekday(tempDate) = 7 Then
			tempDate = tempDate + 2
		End If
		iCount = iCount + 1
	Loop
		
	Me.NewDate = tempDate
	
End Sub

[okosh]

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.

 
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
		If Weekday(tempDate) = 7 Then
			tempDate = tempDate + 2
		End If
		iCount = iCount + 1
	Loop
		
	Me.NewDate = tempDate
	
End Sub

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