First, here is a function that will calculate the working days between two
dates. It also includes reference to a holiday table to eliminate holidays
from the count. You can either create such a table or remove the code for
the holidays.
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
On Error GoTo CalcWorkDays_Error
'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")
CalcWorkDays_Exit:
On Error Resume Next
Exit Function
CalcWorkDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit
End Function
*****************
Now, you don't actually have to have a command button, you can create a
functioin that determines which date to use, then calls the function above
and returns the value. Then make the following function the control source
of a text box to show the number of days. Now, since it will be the control
source, you can't update a table field with the value, but that is good.
This is a calculated value and calculated values should not be stored in
tables
Private function GetTurnTime() as Long
Dim dtmTurnDate as Date
If IsNull(Me.txtFundingDate) Then
If IsNull(Me.txtExecScanDate) Then
GetTurnTime = 0
Else
dtmTurnDate = Me.txtExecScanDate
End If
Else
If IsNull(Me.txtExecScanDate) Then
dtmTurnDate = Me.txtFundingDate
Else
If Me.txtFundingDate < txtExeScanDate Then
dtmTurnDate = Me.txtFundingDate
Else
dtnTurnDate = Me.ExecScanDate
End If
End If
End If
If IsNull(Me.OrigPkDate) Then
GetTurnDate = 0
Else
GetTurnDate = CalcWorkDays(Me.OrigPkDate, dtmTurnDate)
End If
End Function
--
Dave Hargis, Microsoft Access MVP
Stockwell43 said:
Hello,
I ask this question already but afraid I might have not been clear so I am
going to try and simplify it.
All I want to do is click on my turntime button and have EITHER the Funding
Date or the Excecuted Scan Date whichever field is completed and subtract it
from the Original Package Rec. Date to calculate the number of working days
without weekends.
Also, If the Executed Date and Funding date are both completed, then I would
like the code to use the ealier date.
Is this at all possible?
Thanks!!
