Calculate # of Business Days

[Mike]

Hi,

Yesterday I went to
http://www.mvps.org/access/datetime/date0012.htm and
copied the code below, however I cannot seem to convert it
to work in my application. My field names are Receive and
SchedD. Any help would be greatly appreciated.

Thank you

Mike

Function Work_Days (BegDate As Variant, EndDate As
Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function

 

[John Spencer (MVP)]

Simple.

You should have copied that code to a module and saved it. The Module name
SHOULD NOT be the same as the function name.

Then whenever you need to use it, you pass it two parameters. Since this is the
queries group, you would probably need add this to a field cell in the query
grid; Something like -

Field: WorkDays: Work_Days([SchedD],[Recieve])
Table: <<Leave blank>>

Hope this helps.