Calculate businness Days

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to incorporate the code for calculating for number of working
days to a query but i am new to code or edits :

'*********** Code Start **************
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

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

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 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

I have a query that has fields RecvdDate and InvDate so how do i change the
above or better yet where in my query is it placed?
 
You place your code in a module. once it is there you can call/use it in your
forms, reports and queries.

Open you query in deisgn mode and create a new column named 'NumWrkDays'
(you can always changes the name at a later point). and then build an
expression using the function. Your expression will end up looking like:

NumWrkDays:WorkingDays([InvDate], [RecvdDate])
 
Back
Top