Calculate Business Days

[snoopy92211]

Help! I have a module that shows how to calculate business days. However, I don't know how to refer to it.


I have a table (named ITEMS) with values
ID Date Received Date Submitted
1 1/1/2004 2/3/2004
2 2/1/2004 1/31/2004
3 3/3/2004 3/6/2004

Using the information from the table, I need to calculate the net business days between received and submitted for each record.

Then I need to actually sum the total business days for all rows and calculate an average number of business days for the entire table. I need this information in a report. How can I do this?

Here's the module I'm using:

On Error GoTo Err_WorkingDays

Dim intCount As Integer

'ReceviedfromIRS = ReceviedfromIRS + 1
'If you want to count the day of ReceviedfromIRS as the 1st day
'Comment out the line above

intCount = 0
Do While ReceviedfromIRS <= DateSubmitted
'Make the above < and not <= to not count the DateSubmitted

Select Case WeekDay(ReceviedfromIRS)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
ReceviedfromIRS = ReceviedfromIRS + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case err

Case Else
MsgBox err.Description
Resume Exit_WorkingDays
End Select

End Function

Thanks!

 

[dhookom]

Create a new, blank module and paste the code from

http://www.mvps.org/access/datetime/date0006.htm

into it. Save the module as 'basDateFunctions'. You can then use the function in a query, code, or control source like any other function.

I'm a bit confused because your samples are consistently one earlier than the other.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[snoopy92211]

I'm a bit confused because your samples are consistently one earlier than the other."

Now I'm confused. What do you mean?

Either way, often times I post something then I figure it out 15 minutes later. I guess I posted all of that above to say...

For a control (Textbox in a report), I would like to use DSUM. However, I'm not quite sure how to use the query that I want to use as the domain.

My Query is:
SELECT Item.Recevied, Item.DateSubmitted, workingdays([Recevied],[DateSubmitted]) AS BusinessDays
FROM IRSItem;

I would like to Sum the totals of the BusinessDays field i created through the query, then Average them.

 

[dhookom]

[tt]
ID Date Received Date Submitted
1 1/1/2004 2/3/2004
2 2/1/2004 [red]1/31/2004[/red] <==Earlier than received
3 3/3/2004 3/6/2004
[/tt]
You should be able to Sum the businessDays in your report or even average them in a group or report footer.
=Sum([BusinessDays])
=Avg([BusinessDays])


Duane
MS Access MVP
Find out how to get great answers faq219-2884.