Posts Tagged ‘Microsoft Access sample’

Microsoft Access function to calculate an end date for employee leave

Wednesday, September 28th, 2011

This is a generic function you can use to calculate the end date for employee leave given a start date and a number of days.  It allows you to calculate the end date taking into account holidays and weekends.
First set up a table to store holiday dates.  We called it tblHoliday and it had a field HolidayDate.  You might also want to add fields for the name of the holiday and a primary key (although the date is a unique field).
Next add a form with three text boxes (txtStartDate, txtEndDate, txtNoOfDays) and a button (btnCalc).  The code for the form is as follows.

Private Sub btnCalc_Click()
Dim dteCalcEndDate As Date                                      ‘ Date to be calculated

dteCalcEndDate = funCalcEndDate(Me.txtStartDate, Me.txtNoOfDays) ‘ Pass the start and number of days
Me.txtEndDate = dteCalcEndDate                                  ‘ Put the end date on the form

End Sub

Function funCalcEndDate(dteStart As Date, lngNoOfDays As Long) As Date
Dim dteTest As Date                                             ‘ Date to be tested
Dim intCounter As Integer                                       ‘ Count the number of days

dteTest = dteStart                                              ‘ Set it to the start date
intCounter = 0                                                  ‘ Count the number of valid days

Do While intCounter < lngNoOfDays                               ‘ Loop until you have found valid days
If Weekday(dteTest) > 1 And Weekday(dteTest) < 7 Then       ‘ 1 = Sun, 7 = Sat
‘ Test if a holiday is found in the holiday table
If IsNull(DLookup(“HolidayDate”, “tblHoliday”, “HolidayDate=#” & Format(dteTest, “mm/dd/yyyy”) & “#”)) Then
intCounter = intCounter + 1                         ‘ This is a valid date
funCalcEndDate = dteTest                            ‘ Update the function
End If
End If

dteTest = DateAdd(“d”, 1, dteTest)                          ‘ Select the next day

End Function

The key things in the code are the use of weekday.  In Microsoft Access, weekday is 1 to 7 starting on Sunday.  Sunday is 1 and Saturday is 7.  The other check is to the dLookUp to see if the date being tested exists in tblHoliday.  If it doesn’t it returns a null.

This function was originally created to check that the start and end dates corresponded to the number of leave days for staff.  You could use it for any calculation of days.  For example in a project if a task starts on a certain date, and takes so many days, when does it end?  In a purchasing system, when is a delivery due given a certain order day and leadtime for delivery.