Passing null arguments to a function

[mbentley]

I have created a function that takes the City, Province, and Postal Code fields and creates one address based on the entries. Works OK, except I get "#Error" returned in my forms or queries if there is a null value in any one of the arguments. I've accounted for nulls in my code, and it works fine if I use Nz in the formulas that call the function. By stepping through the code, I've figured out that it does not even run if a null vaue is passed to one of the arguments. I don't want to have to remember to use Nz for all the arguments I pass, so I'm wondering if there's a trick to getting it to allow null arguments.

 

[RichMorrison]

Mike,

If you have Access 97 or later, you can use the "Optional" keywork in the declaration of parameters in a function/subroutine.

See Help for more details.

RichM

 

[mbentley]

Thanks Rich. I actually tried that. It didn't work. I also tried setting a default value, which didn't work either (see below). Any other ideas?

Tried:

Function FullAddress (strCity As String, strProv As String, strPC As String) As String

Function FullAddress (Optional strCity As String, Optional strProv As String, Optional strPC As String) As String

Function FullAddress (Optional strCity As String = "", Optional strProv As String = "", Optional strPC As String = "") As String

 

[pono1]

Mike,

Experiment with something like this:

Function CombineAddress(Optional City As String = "No City", _
Optional Province As String = "No Province", _
Optional pCode As String = "No pCode")

'TEST
'has the province default changed?
    If province = "No Province" Then 'No? arg not passed
        province = ""
        Debug.Print "The province Value wasn't passed"
    End If

    Dim strNewAddress As String
'tie together strings
    strNewAddress = City & ", " & Province & " " & pCode

'assign this new address string to function's value
    CombineAddress = strNewaddress

End Function

Your calling statements must use a comma to denote the position of the arguments. So if, say, you had city and postal code data but no province value, you would write a statement like this to call the function.

Dim strAddress as String
strAddress = CombineAddress ("Toronto", , "KX7629")

debug.print strAddress

Regards,
Tim

 

[Rich]

I've just tried this without any problems
Function FullAddress(Optional strCity, Optional strProv, Optional strPC As Variant) As String
FullAddress = strCity & vbCrLf & strProv & vbCrLf & strPC
End Function
didn't matter if there was no data in any or even all of the fields

 

[mbentley]

Problem solved

Thanks for the help, everyone. Turns out it had to do with the variable type of the arguments. When set to Variant, as in Rich's post, it allows null values, but when set to String, as in my post, it won't. I'd rather not use Variants, but if it works, it works!

Thanks again.

By the way, it wasn't necessary to use the Optional qualifier after all.

 

[RichMorrison]

Mike,

As far as I know, the variable data type should not matter.

In Access 2K I have used things like
(Optional strSomething As String = "", etc)
with no problem.

I have not tried this with Acc97 or whatever the latest version might be.

RichM

 

[mbentley]

I'm using Office XP, so Access 10 or 2002. Normally I'd agree with you, but using the Optional statement with a default value doesn't work whereas setting the argument to variant does. Can't argue with results.