Web Development



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

By 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

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
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
total = counter
End If
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?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Why not use a query instead ?

by Tony Hopkinson In reply to Need help with Access 200 ...

Create table called days
and fill it with some script for a suitable range

Date Type
1/1/2009 W
2/1/2009 H
3/1/2009 D

so d is a working day, w is a weekend and H is Holiday (glaring omission in your function)

Then something like

Selet Job_ID, Start_Date,End_Date, Count(Day_Type) From Jobs,Days
Where Days.Date >= Start_Date and Days.Date <= End_Date
Group By Job_ID,Start_Date,End_Date

Aside from being way quicker, you can have holdiays, you could beef it up with years, Quarter, Period, Month, WeekNumber.

You can go the other way and work out a date 30 day working days in advance etc.

And it you end up switching fromaccess to say another database, it will be much easier, and much much faster.

Related Discussions

Related Forums