Hello,
I am trying to use the code I found here in the forum to calculate an existing date and adding a number of days to get the new business date...I have a query in which I am using the field "DateARAgingRcvd" (which is a date field) and adding 10 to get the new date which would be "DateFirstMailingDue".
I copied the code into a new module, saved and named it and tried to apply it to my query like below...
FirstMailingDue: GetWorkDateDiff ([DateARAgingRcvd],10)
I get this error message: Undefined Function "GetWorkDateDiff" in expression
So I changed Private to Public in the code
Then I get this message:
The expression you entered has a function with the wrong number of arguments.
Below is the code I am using...I am new to code so please forgive me.
Option Compare Database
Option Explicit
Public Sub btnSubmit_Click()
lblResultEndDate.Caption = GetWorkDateDiff(dtpDate.value, txtWorkDays.value, CBool(chkCurrentDay.value))
End Sub
Public Function GetWorkDateDiff(start_date, days, count_first_day As Boolean) As Date
If isValidValue(start_date, "Date") Then
If isValidValue(days, "Number") Then
Dim work_days As Integer: work_days = CInt(days)
Dim due_date As Date: due_date = DateAdd("d", IIf(count_first_day, 0, -1), start_date)
While work_days > 0
due_date = DateAdd("d", 1, due_date)
If Not (Weekday(due_date) = 1 Or Weekday(due_date) = 7) Then: _
work_days = work_days - 1
Wend
GetWorkDateDiff = due_date
Else
MsgBox "Invalid Value, must be non-null and a whole number"
End If
Else
MsgBox "Invalid Value, must be non-null and date value."
End If
End Function
Public Function isValidValue(value, type_name) As Boolean
isValidValue = (Len(value & vbNullString) > 0 And _
IIf( _
TypeName(value) = "string" And type_name = "number", _
IsNumeric(value), _
type_name = TypeName(value) _
) _
)
End Function
I thank you for any help.
Lilly
I am trying to use the code I found here in the forum to calculate an existing date and adding a number of days to get the new business date...I have a query in which I am using the field "DateARAgingRcvd" (which is a date field) and adding 10 to get the new date which would be "DateFirstMailingDue".
I copied the code into a new module, saved and named it and tried to apply it to my query like below...
FirstMailingDue: GetWorkDateDiff ([DateARAgingRcvd],10)
I get this error message: Undefined Function "GetWorkDateDiff" in expression
So I changed Private to Public in the code
Then I get this message:
The expression you entered has a function with the wrong number of arguments.
Below is the code I am using...I am new to code so please forgive me.
Option Compare Database
Option Explicit
Public Sub btnSubmit_Click()
lblResultEndDate.Caption = GetWorkDateDiff(dtpDate.value, txtWorkDays.value, CBool(chkCurrentDay.value))
End Sub
Public Function GetWorkDateDiff(start_date, days, count_first_day As Boolean) As Date
If isValidValue(start_date, "Date") Then
If isValidValue(days, "Number") Then
Dim work_days As Integer: work_days = CInt(days)
Dim due_date As Date: due_date = DateAdd("d", IIf(count_first_day, 0, -1), start_date)
While work_days > 0
due_date = DateAdd("d", 1, due_date)
If Not (Weekday(due_date) = 1 Or Weekday(due_date) = 7) Then: _
work_days = work_days - 1
Wend
GetWorkDateDiff = due_date
Else
MsgBox "Invalid Value, must be non-null and a whole number"
End If
Else
MsgBox "Invalid Value, must be non-null and date value."
End If
End Function
Public Function isValidValue(value, type_name) As Boolean
isValidValue = (Len(value & vbNullString) > 0 And _
IIf( _
TypeName(value) = "string" And type_name = "number", _
IsNumeric(value), _
type_name = TypeName(value) _
) _
)
End Function
I thank you for any help.
Lilly
