Networking Days

[Harris21]

Hello,

I have looked around trying to find a solution to the same old problem of trying to calculate how many Working Days are between two dates in a query, I found some code that works great except when one of the dates is blank, (Brings up a debug error!). This is really annoying me, is there any way around this? Module code used is as follows,

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

Any help would be greatly welcomed!

Simon

 

[KeithG]

I posted a function I wrote to calculate businessdays with out the need for a Holiday table. Search the forum for businessdays.

 

[Harris21]

Thank you for your quick response,

I have used the code stated in your thread for Business Days and created a Module, when i call this from a Query and try to run it, an error appears,

Undefined Function 'BusinessDays' in Expression

Any Ideas?

Simon

 

[Mile-O]

What module have you put the code in? One attached to a form, or a standalone one?

Is the function listed as Public or Private?

Have you ensured that it is written with brackets after it? i.e. MyFunction(), because if you just put MyFunction Access will convert it to "MyFunction" and treat it as a string literal.

 

[Harris21]

I have put the code in a stand alone one that u then call into the query, The Expresion shows as follows,

 Expr1: BusinessDays([Request Received],[WRA Date])

and the Module is set to 'Public Function'

Thanks,

Simon

 

[Harris21]

Still getting an error, any ideas?

Si