Need help with Access 2002 Report from a query and using a functio

[nziese]

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?

 

[Duane Hookom]

I would type "Public " in front of Function. Also you spelled the function
name wrong "Buseness_Days_Between_Dates". I assume the module has a name
other than the name of the function.

 

[Duane Hookom]

I would type "Public " in front of Function. Also you spelled the function
name wrong "Buseness_Days_Between_Dates". I assume the module has a name
other than the name of the function.

 

[Douglas J. Steele]

Duane's indicated how to fix the problem in your question, but I though I'd
point onf a problem with your function.

You've declared the parameters as (ByVal start_date As Date, ByVal end_date
As Date). As such, start_date and end_date can never be Null: the only data
type that can be Null is Variant. Since you're planning on using this
functon as part of a query, it's possible that one or both of your dates
might be Null. Therefore, you should change the declaration to (ByVal
start_date As Variant, ByVal end_date As Variant). As well, Is Null is
reserved for SQL queries.

Instead of If Not start_date Is Null And Not end_date Is Null Then, use If
(IsNull(start_date) Or IsNull(end_date)) = False Then

 

[Douglas J. Steele]

Duane's indicated how to fix the problem in your question, but I though I'd
point onf a problem with your function.

You've declared the parameters as (ByVal start_date As Date, ByVal end_date
As Date). As such, start_date and end_date can never be Null: the only data
type that can be Null is Variant. Since you're planning on using this
functon as part of a query, it's possible that one or both of your dates
might be Null. Therefore, you should change the declaration to (ByVal
start_date As Variant, ByVal end_date As Variant). As well, Is Null is
reserved for SQL queries.

Instead of If Not start_date Is Null And Not end_date Is Null Then, use If
(IsNull(start_date) Or IsNull(end_date)) = False Then