The UDF (user defined function) addWorkDays returns a date based on the parameter "DaysBetween" interval and the parameter "DueDate" date.
Code:
Function addWorkDays(DaysBetween As Long, DueDate As Date) As Date
' where "DaysBetween" is an interval selected from a dropdown box, and "DueDate" is a calculated field using "RecdDate" and "DaysBetween"
'********************
'Code Courtesy of
' Paul Eugin
'********************
Dim RecdDate As Date 'where "RecdDate" is the date the RFQ was received
'in this function, the variable "RecdDate" is not used, so it can be deleted
Dim tmpDate As Date
Dim I As Long
tmpDate = DueDate 'is this correct - the "TmpDate" is set to the "RecdDate"?
'No, tmpDate should be set to the parameter "DueDate"
I = 1
Do While I <= DaysBetween
' I don't like using the continuation line, so I changed the IF() function to the block syntax.
If Weekday(tmpDate) <> 1 And Weekday(tmpDate) <> 7 And DCount("*", "tbl_Holidays", "HolidayDate = " & CDbl(tmpDate)) = 0 Then
I = I + 1
End If
'I understand that this is checking the "RecdDate" to determine if it is a Saturday or Sunday (because "tmpDate" = "RecdDate"
' and is looking at the "Holidays" table and performing an aggregate count of "HolidayDate" that matchup in the "Holidays" table.
What is "CDbl(TmpDate))?
'No, this is checking if "tmpDate" is a weekend day. If "tmpDate" is not Sat or Sun AND "tmpDate" in not in tbl_Holidays, the use the DateAdd function to a one day to tmpDate.
tmpDate = DateAdd("d", 1, tmpDate)
Loop
addWorkDays = tmpDate 'What is this doing?
'This is how you return the value of calculations in a function.
End Function
What is "CDbl(TmpDate))?
The function CDbl() converts the parameter to a number- double type. Even though you see a "Date" in a table, it is actually stored as a double number type. Access has code to show you the number as a Date even though it is a number. This is why you can add 1 to a date and get a date.
If you had code that looked like
Code:
Dim tmpDate as Date
tmpDate = Date()
tmpdate = tmpdate + 1
executing the code would return tomorrow's date.
In the immediate window, try
? date()+1
For clarity, here is the code
Code:
Function addWorkDays(DaysBetween As Long, DueDate As Date) As Date
'********************
'Code Courtesy of
' Paul Eugin
'********************
Dim tmpDate As Date
Dim I As Long
tmpDate = DueDate
I = 1
Do While I <= DaysBetween
If Weekday(tmpDate) <> 1 And Weekday(tmpDate) <> 7 And DCount("*", "tbl_Holidays", "HolidayDate = " & CDbl(tmpDate)) = 0 Then
I = I + 1
End If
tmpDate = DateAdd("d", 1, tmpDate)
Loop
addWorkDays = tmpDate 'return the date
End Function
that gives these results
Is this what you want???