|
Subject:
|
NetworkDays in Access?
|
|
Posted By:
|
sburgess
|
Post Date:
|
3/15/2006 9:59:37 AM
|
Is there a way for Access to recognize holidays in a function? I found the DateDiffW function that counts just weekdays, but I also need it to count holidays as well, within the same function. Here is the coding that I have so far, and it's working well. What I'm using it for is a "on hold" "off hold" calculation, that will be pulled into measurables. Any help would be appreciated!
Public Function DateDiffW(BegDate, EndDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer
If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case Weekday(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case Weekday(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
End If
End Function
|
|
Reply By:
|
kindler
|
Reply Date:
|
3/15/2006 1:01:08 PM
|
You need to add a table of holidays. From there you can do a select query on the table with dates within the range you're looking at, convert the query into a recordset, and subtract the size of the recordset from the number of days you returned with the DateDiffW function.
|
|
Reply By:
|
sburgess
|
Reply Date:
|
3/15/2006 1:13:05 PM
|
Thanks for the information. I will try it.
I'm still new to programming. on the
NumWeeks = DateDiff("ww", BegDate, EndDate)
is there a way to tell it not to give an error on a "null" value? I'm running a query, and using the DateDiffW in an expression, to give me a total of dates, minus "onhold", "offhold" dates, but there are times that there are no values in the "onhold", "offhold" fields, and when I run it, it is giving me an error on this line.
|
|
Reply By:
|
kindler
|
Reply Date:
|
3/15/2006 3:28:01 PM
|
IIf(IsNull(value),0,value)
or
IIf(IsNull(value),'0',value)
or
IIf(IsNull(value),'',value)
One of those three should work depending on the data type required.. I think.
|
|
Reply By:
|
sburgess
|
Reply Date:
|
3/16/2006 9:30:54 AM
|
I'm sorry to be a pain about this, but I guess I'm not getting this.
Here is the expression line that I have in my query:
Expr2: IIf(IsNull([OnHoldREQ]),DateDiffW([Requisition Rec'd],[Submitted to Manager]),DateDiffW([Requisition rec'd],[Submitted to Manager])-DateDiffW([OnHoldREQ],[OffHoldREQ]))
When I run this, I get an error in my coding, highlighting this line:
NumWeeks = DateDiff("ww", BegDate, EndDate)
I can understand a little what you're explaining, but I'm getting lost when I'm trying to write it into my code.
I started under the NumWeeks code line with:
IIf(IsNull(BegDate),' ',
but then I get stuck, telling it what to do from that point. Any help would be great!! 
|
