calculation workdays between 3 dates

[wilkob]

I have a table with 3 columns

Order Date (f.e. 05/10/2006)
Due Date (f.e. 09/10/2006)
Ship Date (f.e. 10/10/2006)

I would like to calculate:

number of working days between order date and due date
number of working days between due date and ship date

Can someone help me with this one?

 

[KeithG]

Look up the datediff function in Access help. In a query use something like below

datediff('w',[StartDate],[EndDate])

 

[RuralGuy]

Here's a function you cah use:

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'-- Return the number of WorkingDays between StartDate and EndDate
On Error GoTo err_workingDays

Dim intCount As Integer

If IsDate(StartDate) And IsDate(EndDate) Then
   If EndDate >= StartDate Then
      '-- Force Incoming Dates into consistant format
      StartDate = CDate(Format(StartDate, "Short Date"))
      EndDate = CDate(Format(EndDate, "Short Date"))
      
      intCount = 0
      Do While StartDate < EndDate
         StartDate = StartDate + 1
         If Weekday(StartDate, vbMonday) <= 5 Then
      '-- Use the following code if you have a "Holiday" table
      '   If Weekday(StartDate, vbMonday) <= 5 And IsNull(DLookup("[Holiday]", "tblHolidays", "[HolDate] = #" & StartDate & "#")) Then
            intCount = intCount + 1
         End If
      Loop
      WorkingDays = intCount
   Else
      WorkingDays = -1  '-- To show an error
   End If
Else
   WorkingDays = -1  '-- To show an error
End If

exit_workingDays:
   Exit Function

err_workingDays:
   MsgBox "Error No:    " & Err.Number & vbCr & _
   "Description: " & Err.Description
   Resume exit_workingDays

End Function

 

[wilkob]

First let me appologize for being a total noob, but where do I enter this code???

Is this in the query?

 

[RuralGuy]

You put the code in a standard module named basDates. Then you can use it in your query like: WorkingDays([StartDate], [EndDate])

 

[wilkob]

this did the trick.

THANKS !!!!

 

[RuralGuy]

Glad I could help.