Weekday( ) function troubles

[Chris B]

I need to show the number of workdays between a given set of dates. How do I link this as the control source on a report so that it picks up the BegDate and EndDate I have entered as parameters for the report? This is the code I have:

Function WorkDays(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
'

 

[Roni Sutton]

You can launch your code on the 'on format' portion of the properties for the section of the report where you want to display the information. Create two unbound text boxes in that section. At the end of your code, set each text box to the begin and end date variables.

To get the begin and end dates initially so you can use them, don't put the criteria in the query that asks for that information. Instead, create a form with begin and end date fields. Reference those fields by the name - [forms]![nameofyourform]![nameofthefield]. You can use this format in the criteria section of your query, in your code for your report and in the report itself.

Hope this helps you.

Roni