Calculations (1 Viewer)

[Danny]

I have an eligibility tracker database designed in access 2000. The employees have to process the applications within 10 business days.
1. How do you determine business days, weekends and holidays?
2. If I have the following fields: AppStatus, EligDay, EligDate, AppReceived, Reassess etc. AppStatus has a dropdown of (NEW, EAP, REACTIVATE etc.)

How do I do the following calculations:

When you select NEW from AppStatus dropdown,
EligDay = EligDate – AppReceived

When you select REACTIVATE from AppStatus dropdown,
EligDay = EligDate -Reassess

Thanks in advance!
DKD

 

[Vassago]

This code was given to me not that long ago for a calculation...



Option Compare Database
Option Explicit

'From Microsoft KB article Q115489
'=============================================
' The DateAddW() function provides a workday substitute
' for DateAdd("w", number, date). This function performs
' error checking and ignores fractional Interval values.
'=============================================
Function DateAddW(ByVal TheDate, ByVal Interval)

Dim Weeks As Long, OddDays As Long, Temp As String

If VarType(TheDate) <> 7 Or VarType(Interval) < 2 Or _
VarType(Interval) > 5 Then
DateAddW = TheDate
ElseIf Interval = 0 Then
DateAddW = TheDate
ElseIf Interval > 0 Then
Interval = Int(Interval)

' Make sure TheDate is a workday (round down).
Temp = Format(TheDate, "ddd")
If Temp = "Sun" Then
TheDate = TheDate - 2
ElseIf Temp = "Sat" Then
TheDate = TheDate - 1
End If

' Calculate Weeks and OddDays.
Weeks = Int(Interval / 5)
OddDays = Interval - (Weeks * 5)
TheDate = TheDate + (Weeks * 7)

' Take OddDays weekend into account.
If (DatePart("w", TheDate) + OddDays) > 6 Then
TheDate = TheDate + OddDays + 2
Else
TheDate = TheDate + OddDays
End If

DateAddW = TheDate
Else ' Interval is < 0
Interval = Int(-Interval) ' Make positive & subtract later.

' Make sure TheDate is a workday (round up).
Temp = Format(TheDate, "ddd")
If Temp = "Sun" Then
TheDate = TheDate + 1
ElseIf Temp = "Sat" Then
TheDate = TheDate + 2
End If

' Calculate Weeks and OddDays.
Weeks = Int(Interval / 5)
OddDays = Interval - (Weeks * 5)
TheDate = TheDate - (Weeks * 7)

' Take OddDays weekend into account.
If (DatePart("w", TheDate) - OddDays) < 2 Then
TheDate = TheDate - OddDays - 2
Else
TheDate = TheDate - OddDays
End If

DateAddW = TheDate
End If

End Function

Place this code in a module. For this example, we'll call it findenddate.

Now place this in the after update of the beginning date field or the control source of the field you want to be 10 days out.

IIf(IsNull([BeginDate]), "", DateAddW([BeginDate], 10))

This will skip all weekends. Now Holidays I don't know.


Now, for your combo box conditions, just do.

On the combo box...

In the Event Procedure -> after update

Select Case AppStatus
Case "New"
EligDay = EligDate – AppReceived
Case "Reactivate"'
EligDay = EligDate -Reassess
End Select

Maybe someone else has a better example than me. I figured since no one responded I would lend a hand.

Let me know if that helps.

Vassago