Can I use a user created function in a report?

[nziese]

I have created a report from a query. In a text box in the summary area I
need to calculate workdays. 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 THE REPORT bound to data from
the query.

This is my function

Function Business_Days_Between_Dates(start_date As Date, 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


Which is saved as a module. When I run the report I am prompted for an
input for Buseness_Days_Between_Dates. I'm sure it is because I don't know
how to do this. Anyone able to help?

 

[Clifford Bass]

Hi,

I hope you will not take this as harsh. It would be good to compile
your code in order to discover errors in it (Debug menu, Compile
databasename). You can help the compiler to find problems by adding an
"Option Explicit" at the top of your module. This will force you to declare
all variables. What is that "begin" line? Try getting rid of it. Also,
dates cannot be null. If the column values that you send to the function can
contain nulls, use variant parameters instead. And when testing use the
IsNull() function instead. Or, you can simplify by testing instead to see if
the values are dates with the IsDate() function. This would eliminate the
need to convert the input parameters into dates. Which was not necessary in
your code as the parameters were declared as dates. Also, keep in mind that
a date value can also contain a time component. So a comparison of a start
date of 05/20/2009 2:30 PM to an end date of 05/20/2009 3:30 PM will not be
equal, even though they are on the same day. Finally you do not take into
consideration that the end date might be earlier than the start date, which
can lead to an error.

Here is what I might use:

Public Function Business_Days_Between_Dates( _
ByVal varStart_Date As Variant, ByVal varEnd_Date As Variant, _
Optional ByVal boolIgnoreTime As Boolean = True) _
As Variant

Dim intBusinessDays As Integer
Dim intWeekDay As Integer
Dim dtWork As Date

If IsDate(varStart_Date) And IsDate(varEnd_Date) Then
If boolIgnoreTime Then
' Remove the time component
varStart_Date = Fix(varStart_Date)
varEnd_Date = Fix(varEnd_Date)
End If
If varEnd_Date < varStart_Date Then
' Reverse the dates when end date is prior to start date
dtWork = varStart_Date
varStart_Date = varEnd_Date
varEnd_Date = dtWork
End If
intBusinessDays = 0
For dtWork = varStart_Date + 1 To varEnd_Date Step 1
intWeekDay = Weekday(dtWork)
If intWeekDay > 1 And intWeekDay < 7 Then
' Could also add in a check for holidays from a holidays table
intBusinessDays = intBusinessDays + 1
End If
Next dtWork
Business_Days_Between_Dates = intBusinessDays
Else
Business_Days_Between_Dates = "#Error"
End If

End Function

As long as that is in a regular (not class, not report, not form)
module, your

=Business_Days_Between_Dates(START_DATE,STATUS_DATE)

line, as the control source of a text box, should work.

Hope that helps,

Clifford Bass

 

[Clifford Bass]

Hi,

I hope you will not take this as harsh. It would be good to compile
your code in order to discover errors in it (Debug menu, Compile
databasename). You can help the compiler to find problems by adding an
"Option Explicit" at the top of your module. This will force you to declare
all variables. What is that "begin" line? Try getting rid of it. Also,
dates cannot be null. If the column values that you send to the function can
contain nulls, use variant parameters instead. And when testing use the
IsNull() function instead. Or, you can simplify by testing instead to see if
the values are dates with the IsDate() function. This would eliminate the
need to convert the input parameters into dates. Which was not necessary in
your code as the parameters were declared as dates. Also, keep in mind that
a date value can also contain a time component. So a comparison of a start
date of 05/20/2009 2:30 PM to an end date of 05/20/2009 3:30 PM will not be
equal, even though they are on the same day. Finally you do not take into
consideration that the end date might be earlier than the start date, which
can lead to an error.

Here is what I might use:

Public Function Business_Days_Between_Dates( _
ByVal varStart_Date As Variant, ByVal varEnd_Date As Variant, _
Optional ByVal boolIgnoreTime As Boolean = True) _
As Variant

Dim intBusinessDays As Integer
Dim intWeekDay As Integer
Dim dtWork As Date

If IsDate(varStart_Date) And IsDate(varEnd_Date) Then
If boolIgnoreTime Then
' Remove the time component
varStart_Date = Fix(varStart_Date)
varEnd_Date = Fix(varEnd_Date)
End If
If varEnd_Date < varStart_Date Then
' Reverse the dates when end date is prior to start date
dtWork = varStart_Date
varStart_Date = varEnd_Date
varEnd_Date = dtWork
End If
intBusinessDays = 0
For dtWork = varStart_Date + 1 To varEnd_Date Step 1
intWeekDay = Weekday(dtWork)
If intWeekDay > 1 And intWeekDay < 7 Then
' Could also add in a check for holidays from a holidays table
intBusinessDays = intBusinessDays + 1
End If
Next dtWork
Business_Days_Between_Dates = intBusinessDays
Else
Business_Days_Between_Dates = "#Error"
End If

End Function

As long as that is in a regular (not class, not report, not form)
module, your

=Business_Days_Between_Dates(START_DATE,STATUS_DATE)

line, as the control source of a text box, should work.

Hope that helps,

Clifford Bass