Optional Argument Error

[jsdba]

Hi,

I keeping getting the error "Invalid use of Null" when i call my email function using optional parameters.

code:

Email Me.ProjectID.Column(1), _
Me.ProjectAddress, _
Me.TaskDescID.Column(1), _
Me.TaskDescExt, _
strStatus, _
Me.TaskStatusID.Column(1), _
Me.PMEmpID.Column(1), _
Me.FieldEmpID.Column(1), _
Me.PMEmpID.Column(3), _
Me.FieldEmpID.Column(3), _
Me.Notes

Public Function Email(ProjectNum As Long, _
                        ProjectAddress, _
                        Optional ByVal TaskDesc As String = "***", _
                        Optional ByVal TaskDescExt As String = "***", _
                        Optional ByVal strStatus As String = "***", _
                        Optional ByVal TaskStatus As String = "***", _
                        Optional ByVal ProjectManager As String = "***", _
                        Optional ByVal FieldManager As String = "***", _
                        Optional ByVal PMEmail As String = "***", _
                        Optional ByVal FMEMail As String = "***", _
                        Optional ByVal Notes As String = "***")

What am i doing wrong here?

 

[spikepl]

What is unclear in the error message itself?

 

[jsdba]

I thought that idea is that Optional allows me to use defaults i.e "***" if my parameters are null.

 

[James Deckert]

Optional allows you to not send that parameter to the function.
Thus you can call your function as
"Email Me.ProjectID.Column(1), Me.ProjectAddress"
since only the first two arguments are required.
I think the solution is to convert your NULLs when calling the function such as Email Me.ProjectID.Column(1), _
Me.ProjectAddress, _
NZ(Me.TaskDescID.Column(1),"***"), _
NZ(Me.TaskDescExt,"***").

 

[jsdba]

Optional allows you to not send that parameter to the function.
Thus you can call your function as
"Email Me.ProjectID.Column(1), Me.ProjectAddress"
since only the first two arguments are required.
I think the solution is to convert your NULLs when calling the function such as Email Me.ProjectID.Column(1), _
Me.ProjectAddress, _
NZ(Me.TaskDescID.Column(1),"***"), _
NZ(Me.TaskDescExt,"***").

I guess i misunderstood how Optional works.

Email Nz(Me.TaskDescID.Column(1),"***") doesnt work when i use it in calling the email function. Had to do

Dim strTaskDesc As String
strTaskDesc = Nz(Me.TaskDescID.Column(1), "***")
Email strTaskDesc