Work Days

[904allen]

I have been developing a program for the investigators in our department to
use. This is not part of my job I just wanted to see if I could do it. While
I have to say I put together a slick program and its all do to the
information and help I received from all of you here. Thank You and keep up
the good work. I have one more task I want to be able to set alerts on the
opening form to let the investigator know that they have to do something on a
case but I need to be able to set work days on the case form. I have several
dates that are updated and when the time expires the investigator needs to
work the file. So my final request is help in this last area. In reading one
of the many post over the last several weeks I came across this function by
Dave Hargis, which I copied and put into my program. But I don’t know how to
implement it. Can you help.
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = dtmReturnDate
End Function

 

[aaron.kempf]

Store your dates information in a Dates Table.

It's called 'Dimensional Modeling'.

Keep up the good work and keep trying.. Honestly-- I think that the
idea of 'This is not part of my job I just wanted to see if I could do
it' is the _ONE_ weapon that our country has...

Against continued escalation of outsourcing.

A -LOT- of people in the good ole U.S. of A. are _TERRIFIED_ to be
seen as 'geeky'.
Honestly.

_NONE_ of my friends will program. Just because of the stigma.

What is that all about?

I honestly encourage you to continue to learn more about databases..
Having a centralized 'Dates Table' is a great way to use this logic in
200 different places.. without having a hard time maintaining it.

When I first started in the DB world.. these people had the audacity
to 'change fiscal calendars' on me.

They went from a kinda wierd 53 week year to a standard ISO 4-4-5.

I ran around like a chicken with my head cut off.. Having a
centralized date table would have made this _MUCH_ easier.

HTH

-Aaron

I have been developing a program for the investigators in our department to
use. This is not part of my job I just wanted to see if I could do it. While
I have to say I put together a slick program and its all do to the
information and help I received from all of you here. Thank You and keep up
the good work. I have one more task I want to be able to set alerts on the
opening form to let the investigator know that they have to do something on a
case but I need to be able to set work days on the case form. I have several
dates that are updated and when the time expires the investigator needs to
work the file. So my final request is help in this last area. In reading one
of the many post over the last several weeks I came across this function by
Dave Hargis, which I copied and put into my program. But I don't know how to
implement it. Can you help.
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = dtmReturnDate
End Function

 

[904allen]

Thank you for your comments, I love doing this so theres no way I going to
stop so call me a geek. sorry to sound stupid but i set up a table called it
dates, but i'm not sure how to proceed, my form that is controled by a query
is where the dates are that I need. Do I attach the dates table to this query
and have the dates from these feilds put in the dates table? Is there any
thing I need to do to the dates table?