Work Day Calculation Question

[sojo]

Below is a Function to calculate the number of workdays between to dates.
The code came from: http://www.mvps.org/access/, so the author gets full
credit. There is so much great info available on sites like these!

My question. How do I have the result displayed on a form? In otherwords, I
want to have three fields on the form. One showing the startdate, the
second enddate, and the third, an unbound field which would show the result
based on the code below. Hope this question makes sense. I recently began
using Access 2000 at work and have been on a huge learning curve ever
since. I understand (more or less) how the code works, but can't figure out
how to associate it with the three fields mentioned above.

Thanks.


'*********** Code Start **************
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
'*********** Code End **************

 

[Graham R Seach]

You would need to add a call to Work_Days(). Where you put that call will
depend on how and when BegDate and EndDate are arrived at. Assuming these
two dates are returned when you browse to each record, you could put the
call in the form's Current event:
Private Sub Form_Current()
Me!txtWorkDaysBetweenDates = Work_Days(Me!txtStartDate,
Me!txtEndDate)
End Sub

There's also this one, which is an alternative to Work_Days():
Private Sub Form_Current()
Dim Date1 As Date, Date2 As Date

Date1=Me!txtStartDate : Date2 = Me!txtEndDate
Me!txtWorkDaysBetweenDates = _
DateDiff("d", Date1, Date2) - _
Choose(Weekday(Date1, vbMonday), 0, 0, 0, 0, 0, 2,
1) - _
Choose(Weekday(Date2, vbMonday), 0, 0, 0, 0, 0, 1,
2) - _
IIf(Weekday(Date2, vbMonday) - Weekday(Date1,
vbMonday) < 0, 2, 0) - _
DateDiff("w", Date1, Date2) * 2
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Graham R Seach]

It seems the code I gave is a tad buggy, so please don't rely on it.

Please see http://www.mvps.org/access/datetime/date0006.htm for an accurate
function.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html