Converting Days into Days

Hi Freinds,


I have a TextBox- txtNumDays which is associated with TestStartDate and



TestEndDate. If you put Number of Days and TestStartDate, it calculates



the TestEndDate. I also have VacationBeginDate and VacationEndDate on a



different form and different table, I want the txtNumDays to calculate
the TestEndDate by Subtracting the number of days between
VacationBeginDate and
VacationEndDate. The number of days between VacationBeginDate and
VacationEndDate is provided by the users on TesterVacationForm.


I am using the following module to calculate the TestEndDate:
Public Function MyAdd(dtStart As Date, intDays As Integer) As Date


Dim dtEnd As Date
Dim I As Integer
Dim dtMiddle As Integer


Dim frmTesterVacation As Form_frmTesterVacation
Dim frmSystem As Form_frmSystem
Dim dbo_TESTERNME As TableDef
Dim NUMOFDAYS As Field


I = 0
dtEnd = dtStart


If intDays 1 Then
Exit Function
End If


Do While I intDays
If Nz(Form_frmSystem.TESTER_NME_ID) "" Then


dtMiddle = [Form_frmTesterVacation].NumberofDays


If dtMiddle = -1 Then
Exit Do
End If
End If


dtEnd = dtEnd + dtMiddle + 1


Select Case Weekday(dtEnd)
Case 2 To 6
' regular week day.MON - Fri..count one day
I = I + 1
End Select
Loop


MyAdd = dtEnd


End Function


If possible please solve this issue, i would really really appreciate
it.

Here are two functions. AddWorkDays returns the date n days from the date
passed to the function. CalcWorkDays returns the number of days between two
dates. Both use a table named Holidays with a field named Holdate. It
excludes any dates found in the table as holidays. If you have a holiday
table in your system, you can change the names where necessary, or you can
create a table, or you can modify the code so it does not check the holiday
table for holidays.

********************************
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim intNotADay As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is = 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) = 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function
*************************************************
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
'D Hargis
'Calculates the number of working days between two dates
'dtmStart - the first day to include in the range
'dtmEnd - the last day to include in the range
'Returns the number of working days between the two dates
'Both dates are counted if they are working days

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday dtmEnd
If Weekday(dtmToday, vbMonday) 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function
Function LastFriday(dtmBaseDate As Date) As Date
'Finds the date of the Last Friday of the month for the date entered
Dim intCurrMonth As Integer
Dim intNextMonth As Integer
Dim intNextYear As Integer
Dim blnAllDone As Boolean
' Find the Current and Next Months
intCurrMonth = Month(dtmBaseDate)
intNextYear = year(dtmBaseDate) + 1
intNextMonth = intCurrMonth + 1
' Find the Friday for the date passed
dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate),
dtmBaseDate)
If Month(dtmBaseDate) = intNextMonth Then
'The Friday for the date entered is in the next month
'so subtract a week and it will be the last Friday
LastFriday = DateAdd("ww", -1, dtmBaseDate)
Exit Function
End If
If Month(dtmBaseDate) intCurrMonth Then
'The Friday for the date entered is in the previous month
'so add a week to get back to current month
'Used instead of because in Jan(1) you can end up in Dec(12)
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
End If
blnAllDone = False
Do Until blnAllDone
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
blnAllDone = Month(dtmBaseDate) = intNextMonth Or _
year(dtmBaseDate) = intNextYear
Loop 'Until blnAllDone
LastFriday = DateAdd("ww", -1, dtmBaseDate)
End Function
*********************

"FA" wrote:

Hi Freinds,


I have a TextBox- txtNumDays which is associated with TestStartDate and



TestEndDate. If you put Number of Days and TestStartDate, it calculates



the TestEndDate. I also have VacationBeginDate and VacationEndDate on a



different form and different table, I want the txtNumDays to calculate
the TestEndDate by Subtracting the number of days between
VacationBeginDate and
VacationEndDate. The number of days between VacationBeginDate and
VacationEndDate is provided by the users on TesterVacationForm.


I am using the following module to calculate the TestEndDate:
Public Function MyAdd(dtStart As Date, intDays As Integer) As Date


Dim dtEnd As Date
Dim I As Integer
Dim dtMiddle As Integer


Dim frmTesterVacation As Form_frmTesterVacation
Dim frmSystem As Form_frmSystem
Dim dbo_TESTERNME As TableDef
Dim NUMOFDAYS As Field


I = 0
dtEnd = dtStart


If intDays 1 Then
Exit Function
End If


Do While I intDays
If Nz(Form_frmSystem.TESTER_NME_ID) "" Then


dtMiddle = [Form_frmTesterVacation].NumberofDays


If dtMiddle = -1 Then
Exit Do
End If
End If


dtEnd = dtEnd + dtMiddle + 1


Select Case Weekday(dtEnd)
Case 2 To 6
' regular week day.MON - Fri..count one day
I = I + 1
End Select
Loop


MyAdd = dtEnd


End Function


If possible please solve this issue, i would really really appreciate
it.