Access

Modify an entered date to the following business day

Ease of Use

Easy

Version tested with

2002,2003 

Submitted by:

WillR

Description:

This is an Access function that automatically adjusts a user-entered date to the next working day. It takes into account holidays entered into a holidays table and adjusts accordingly. 

Discussion:

Let's say you have a form for a project. The start date & end date must be a working day & if the day the user enters is either a weekend date or a holiday, you want the date to adjust to the next working day. 

Code:

instructions for use

			

Option Compare Database Option Explicit Public Function fModBusDay(ByVal dDay As Date) As Date 'Returns days to add to specified date depending 'if day selected is a weekend or exists in tbl_holidays Dim stSQL As String Dim rst As ADODB.Recordset Dim lAdd As Long Dim dHol As Date 'first we get to the monday if dDay is Sat or Sun TestWeekDay: Select Case Weekday(dDay, vbMonday) Case 1 To 5: lAdd = 0 Case Is = 6: lAdd = 2 Case Is = 7: lAdd = 1 End Select dDay = DateAdd("d", lAdd, dDay) 'then we check to see if the revised date is a holiday stSQL = "SELECT HolDate FROM tbl_Holidays WHERE HolDate = #" & dDay & "#" Set rst = CurrentProject.Connection.Execute(stSQL, , adCmdText) 'i.e. if a record is returned If Not rst.BOF Then dHol = rst(0) rst.Close End If 'then check if dday is a holiday If dHol = dDay Then 'if so add another day and re-test dDay = DateAdd("d", 1, dDay) GoTo TestWeekDay Else ' if not, we have a valid day & can exit fModBusDay = dDay GoTo ExitHere End If ExitHere: End Function

How to use:

  1. Download the sample file
  2. Open the form fm_project
  3. Enter data
  4. To add holiday date to the database, open tbl_holdays & add further dates
 

Test the code:

  1. Entering a date that is a Saturday, a Sunday, or a Holiday Date shoudl prompt the data to be adjusted accordingly to the following available business day.
 

Sample File:

ModifyWEandHolDate.zip 29.34KB 

Approved by mdmackillop


This entry has been viewed 106 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express