automatically work out working days

[AccessDevJunior]

Hya,

I am wanting to design a form with a unbound text box, i want users to input a date into the box and click a button, the form then displays the dates for the text box date + 4 working days, 9 working days, 14 working days and 29 working days. I have the code below which works out the number of working days between 2 inputted dates, this works fine but it doesnt do what id like.

Is there anyone that understands what i need and can help me with my design, it would be a great help as we are manually looking up these dates on paper calendars?

Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

'Get the number of workdays between the given dates

Dim dbs As DAO.Database
Dim rstHolidays As DAO.Recordset

Dim Idx As Long
Dim MyDate As Date
Dim NumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)


NumSgn = Chr(35)

MyDate = Format(StartDate, "Short Date")

For Idx = CLng(StartDate) To CLng(EndDate)
Select Case (Weekday(MyDate))
Case Is = 1 'Sunday
'Do Nothing, it is NOT a Workday

Case Is = 7 'Saturday
'Do Nothing, it is NOT a Workday

Case Else 'Normal Workday
strCriteria = "[HoliDate] = " & NumSgn & Format$(MyDate, "yyyy-mm-dd") & NumSgn 'Thanks to "RoyVidar" 2/18/04
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
NumDays = NumDays + 1
Else
'Do Nothing, it is NOT a Workday
End If

End Select

MyDate = DateAdd("d", 1, MyDate)

Next Idx

DeltaDays = NumDays

End Function

 

[MichaelRed]

search the FAQ's. There are a couple of variations on the theme, at least one of which does the calculation, including the consideration of Holidays.





MichaelRed