Due Dates

[Julie Gilliam]

Can anyone please tell me how I can set up a field for Due Dates. If they
fall on the weekend then I need for it to default backwards to the Friday
before the weekend and also the same for holidays. I have tried for over a
year to figure out out to make this work and I finally just gave up.... I
need step by step instuctions on this because what I have tried to do by a
book I have is not working..... Thanks

 

[Clifford Bass via AccessMonster.com]

Hi Julie,

You can use this function which is a slight modification of a previous
business day function that I recently posted for someone else:

=======================================

Public Function BusinessDay(varDate As Variant) As Variant

Dim rsHolidays As DAO.Recordset

On Error GoTo Handle_Error

If IsNull(varDate) Then
BusinessDay = Null
Else
If IsDate(varDate) Then
varDate = CDate(varDate)
Set rsHolidays = CurrentDb.OpenRecordset( _
"select HolidayDate " & _
"from tblHolidays", _
DAO.RecordsetTypeEnum.dbOpenSnapshot)
With rsHolidays
Do While True
If Weekday(varDate, vbMonday) <= 5 Then
.FindFirst "[HolidayDate] = #" & Format(varDate,
"yyyy-mm-dd") & "#"
If .NoMatch Then
Exit Do
End If
End If
varDate = varDate - 1
Loop
End With
BusinessDay = varDate
Else
If Trim(varDate) = vbNullString Then
BusinessDay = Null
Else
BusinessDay = "#Error (Not a date.)"
End If
End If
End If

Exit_Function:
On Error Resume Next

If Not rsHolidays Is Nothing Then
rsHolidays.Close
Set rsHolidays = Nothing
End If
Exit Function

Handle_Error:
BusinessDay = "#Error (" & err.Description & ")"
Resume Exit_Function

End Function

=======================================

It references a table named "tblHolidays" with a single date/time field
named "HolidayDate".

In your form or report or whatever you can use the following as a text
box's control source:

=BusinessDay([Due Date])

Or on your form you can use an After Update event on the Due Date field
to update it immediately.

[txtDue Date] = BusinessDay([txtDue Date])

Clifford Bass