Handling incorrect number of variables passed into a function

[CES]

All,

I'm trying to figure out how to deal with the possibility of a null value being passed into a function.
I'm using the following call to a function in an MS Access form "=getFullName([Field])" the problem seems to be if the field does not have a value, I'm receiving an #Error message in the control box ??? - (because I'm passing in an incorrect number of variables into the function) - ??? I know I can use an IIF() on the form control, but I like to figure out how to do it within the module itself.

I know how to suppress the error by using:

On Error Resume Next
On Error GoTo 0.

But I can't figure out how to then work that into an IF statement that tests for an error and if an error occurs, clears the function or if there is no error It does something. I know the answer lies in error handling, but I can't seem to figure out how to correctly coded it. The code below gives you an idea of what I am trying to accomplish, but in no way works.

Thanks in advance. - CES

Public Function getFullName(tmp As String) As String

If Err.Number <> 0 Then
On Error Resume Next
On Error GoTo 0
getFullName = "0"
Else
getFullName = tmp

End Function

 

[John Vinson]

I'm trying to figure out how to deal with the possibility of a null value being passed into a function.

Use

Public Function getFullName(tmp As Variant) As String

If you dim the parameter as a variant, it CAN accept NULL values, and
you can test for it:

If IsNull(tmp) Then
<do something appropriate>
Else
<do something with the string value in the Variant variable>
End If

John W. Vinson[MVP]

 

[BruceS]

CES,

Try

=getFullName(Nz([Field],0)) for a numeric field or

=getFullName(Nz([Field],"")) for a string field

OR make the parameter optional in getFullName and handle with IsMissing.

Private Function getFullName (Optional myVar as [String/Long/etc]) As String

If IsMissing (myVar) Then
getFullName = ""
Exit Function
End If

'Do what you normally do.

End Function

Bruce

 

[Anthony Jones]

CES,

Try

=getFullName(Nz([Field],0)) for a numeric field or

=getFullName(Nz([Field],"")) for a string field

Nz is good.

OR make the parameter optional in getFullName and handle with IsMissing.

This won't work. Passing a null is not the same as passing a Missing.

Private Function getFullName (Optional myVar as [String/Long/etc]) As String

If IsMissing (myVar) Then
getFullName = ""
Exit Function
End If

'Do what you normally do.

End Function

Bruce

All,

I'm trying to figure out how to deal with the possibility of a null value being passed into a function.
I'm using the following call to a function in an MS Access form

"=getFullName([Field])" the problem seems to be if the field does not have a
value, I'm receiving an #Error message in the control box ??? - (because
I'm passing in an incorrect number of variables into the function) - ??? I
know I can use an IIF() on the form control, but I like to figure out how to
do it within the module itself.tests for an error and if an error occurs, clears the function or if there
is no error It does something. I know the answer lies in error handling,
but I can't seem to figure out how to correctly coded it. The code below
gives you an idea of what I am trying to accomplish, but in no way works.

 

[BruceS]

Anthony,
Thanks for catching my IsMissing error. Must have been late...
Bruce

CES,

Try

=getFullName(Nz([Field],0)) for a numeric field or

=getFullName(Nz([Field],"")) for a string field

Nz is good.

OR make the parameter optional in getFullName and handle with IsMissing.

This won't work. Passing a null is not the same as passing a Missing.

Private Function getFullName (Optional myVar as [String/Long/etc]) As String

If IsMissing (myVar) Then
getFullName = ""
Exit Function
End If

'Do what you normally do.

End Function

Bruce

All,

I'm trying to figure out how to deal with the possibility of a null value being passed into a function.
I'm using the following call to a function in an MS Access form

"=getFullName([Field])" the problem seems to be if the field does not have a
value, I'm receiving an #Error message in the control box ??? - (because
I'm passing in an incorrect number of variables into the function) - ??? I
know I can use an IIF() on the form control, but I like to figure out how to
do it within the module itself.tests for an error and if an error occurs, clears the function or if there
is no error It does something. I know the answer lies in error handling,
but I can't seem to figure out how to correctly coded it. The code below
gives you an idea of what I am trying to accomplish, but in no way works.

 

[CES]

Anthony,
Thanks for catching my IsMissing error. Must have been late...
Bruce

CES,

Try

=getFullName(Nz([Field],0)) for a numeric field or

=getFullName(Nz([Field],"")) for a string field

Nz is good.

OR make the parameter optional in getFullName and handle with IsMissing.

This won't work. Passing a null is not the same as passing a Missing.

Private Function getFullName (Optional myVar as [String/Long/etc]) As String
If IsMissing (myVar) Then
getFullName = ""
Exit Function
End If

'Do what you normally do.

End Function

Bruce

:

All,

I'm trying to figure out how to deal with the possibility of a null

value being passed into a function.

I'm using the following call to a function in an MS Access form

"=getFullName([Field])" the problem seems to be if the field does not have a
value, I'm receiving an #Error message in the control box ??? - (because
I'm passing in an incorrect number of variables into the function) - ??? I
know I can use an IIF() on the form control, but I like to figure out how to
do it within the module itself.

I know how to suppress the error by using:

On Error Resume Next
On Error GoTo 0.

But I can't figure out how to then work that into an IF statement that

tests for an error and if an error occurs, clears the function or if there
is no error It does something. I know the answer lies in error handling,
but I can't seem to figure out how to correctly coded it. The code below
gives you an idea of what I am trying to accomplish, but in no way works.

Thanks in advance. - CES

Public Function getFullName(tmp As String) As String

If Err.Number <> 0 Then
On Error Resume Next
On Error GoTo 0
getFullName = "0"
Else
getFullName = tmp

End Function

Bruce, Anthony.
Thank you for your help... not only did you solve my problem, but you also answered a couple of questions. -- CES