I am running Access 2002 and I have created a report from a query. I need to calculate workdays in a text boxe in the Job_No footer. I am summing some numbers in the footer and the details are per Job_No. I need to calculate the workdays between two dates that are in the Job_No header. I have written a VB function to do this. How do I then use the function in the report to generate the number?
I have tried using the following for the control source the the text box named WORKDAYS.
=Business_Days_Between_Dates (START_DATE,STATUS_DATE)
START_DATE AND STATUS_DATE ARE TEXT BOXES IN Job_No header and are bound to data from the query.
This is my function
Public Function Business_Days_Between_Dates(ByVal start_date As Date, ByVal end_date As Date) As Integer
Dim counter As Integer
Dim total As Integer
Dim starting_date As Date
Dim ending_date As Date
Dim working_date As Date
Dim daynum As Integer
begin
counter = 1
total = 0
If Not start_date Is Null And Not end_date Is Null Then
ending_date = CDate(end_date)
starting_date = CDate(start_date)
If starting_date = ending_date Then
total = 0
Else
working_date = ending_date – 1
Do While working_date <> starting_date
daynum = Weekday(working_date)
If daynum > 1 And daynum < 7 Then
counter = counter + 1
End If
working_date = working_date - 1
Loop
total = counter
End If
Else
total = -1
End If
Business_Days_Between_Dates = total
End Function
This is saved as a module. When I run the report I am prompted for a parameter value for Buseness_Days_Between_Dates. Anyone able to help?
