Can I pass null argument to function?

[lorirobn]

Hi,

I have code that I use in several reports, so I just put this code into a function and created a module for it.

From my reports, I pass 3 arguments to this function. The problem is, one of the arguments may contain nulls, and this generates an error. Is there a way around this? I would like to be efficient and have the same code in a function, but how can I do this if one field may be empty?

Thanks in advance for any help -
Lori

 

[jebry]

Hi Lori!

I think it is easiest to check for this in the function:

Public YourFunction(YourVar As Variant, etc)

If IsNull(YourVar) Then
StringVar = "" or IntVar = 0 etc
Else
SringVar = YourVar or IntVar = YourVar etc
End If

So you pass the field as a variant which will accept nulls and then check it for a null value and set it to some default if it is null and store it if it isn't.

I hope that is clear.



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[rjoubert]

Just make the parameter optional.

Public Function YourFunction(Optional Param1 as String)

 

[hwkranger]

massage the data before it gets to the function.

Variants are not a good variable to use.
Making this optional Won't fix your problem.

Try wrapping your value in NZ() before it's passed.

example:

Textbox source: =Myfunction(nz([SomeField], ""))

This is an example if your'e passing a string. Replace "" with a 0 if an integer..




Randall Vollen
National City Bank Corp.

 

[rjoubert]

Randall, just out of curiosity, why don't you think having an optional parameter will work? Lori's function accepts three parameters, one of which may be null. Sounds like a great case for an optional parameter. It's the easiest solution.

 

[PHV]

rjoubert, a missing optional argument is not the same as a null value !

 

[rjoubert]

Thanks for the tip PHV !

 

[lorirobn]

Thank you all so much for your responses. I've learned some interesting tips.

Randall's example of using "nz" before passing my string worked like a charm!

Just out of curiosity, why is a variant not a good variable to use?

Lori

 

[Golom]

... why is a variant not a good variable to use?

If NULL is a possible "value" (NULL isn't really a "value",) then a Variant is the only thing you can use. In the SQL environment, fields with other data types (e.g. Numbers, Text) may contain NULLS but in the VBA environment only Variants can be NULL.

Using NZ may "work like a charm" in your specific case as long as NULL and zero have the same meaning in your function. If you had something like this however

Function TestResult (TestScore As Variant) As String
   If IsNull(TestScore) Then
      TestResult = "Test Not Taken"
   Else
      TestResult = IIf(TestScore > 64,"Pass","Fail")
   End If
End Function

Then there's clearly a difference. "0" is a "Fail" while NULL is "Test Not Taken".

 

[hwkranger]

Variants are a 'lazy' way of managing data, in my opinion.

Massaging your data before it is passed eliminates "nulls".

I can honestly say -- in the millions of lines of code I have written, I might have used a variant a total of 2 times.

Encapulation of your data within a user defined object can most time eliminate any need for a variant, as well as increase the reusability of your code.

For Golom's example, you could do the following

Public Function TestResult (byval strTestScore As integer) As String
   If strTestScore < 0 Then
      TestResult = "Test Not Taken"
   Else
      TestResult = IIf(TestScore > 64,"Pass","Fail")
   End If
End Function

Called by

  TestResult(nz([Somefield], -1))

As a side note: built in commands such as DLookup and IIf tend to be less efficient, but assist in code readability.

Randall Vollen
National City Bank Corp.

 

[jebry]

Hi Randall!

I liked you solution to this problem though I will still use the method I posted because I find it more readable for the next person, but maybe that is just me.

But I must differ on your opinion of variants. Like any other programming tool variants have there place. In some cases they are irreplacable like when you want to loop through the ItemsSelected collection of a list box. Sometimes they can help with the ease of the code such as when you want to test a string as a number. You can store it in a variant and test it and send it on as a string. You can make your code much more reusable because you send a fucntion any data type and work with it. It just seems that your position is a little extreme.



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[PHV]

Variants are a 'lazy' way of managing data
Seems you're much a VBA guy than a dba.
Variant is MANDATORY if you want to manage SQL null values, period.

 

[Golom]

And just to add to the confusion ...

There are a few places in VB where you MUST use a variant. For example, saving a recordset Bookmark requires a variant because there is no "Bookmark" data type and a Bookmark is not a conventional data type like long or double.

Another example is a function like the following that returns the maximum value from a series of values supplied to it.

Public Function MaxVal(ParamArray Vals() As Variant) As Variant
    Dim X                           As Variant
    Dim MV                          As Variant
    MV = NULL
    For Each X In Vals
        If Not IsNull(X) Then
           If IsNull(MV) Then 
              MV = X
           Else
              If X > MV Then MV = X
           End If
        End If
    Next
    MaxVal = MV
End Function

That might be called with

TheMax = MaxVal ("A", "B", "C")

or

TheMax = MaxVal ( 1, 2, 3 )

Without variants you would need separate functions to compute a String MaxVal and a Numeric MaxVal.

And, as PHV says, NULLs are a vital part of relational databases and you have no choice to deal with them.

... well actually you do have a choice but that involves assigning special meaning to certain reserved values of a variable as hwkranger has done in his example.

That's generally not a good thing in the DBMS world because the meaning attached to that special value is usually buried in an application's code and is not apparent when looking at the database independent of a particular application.

 

[hwkranger]

Variant is MANDATORY if you want to manage SQL null values, period

isnull(), nz ?

if it's so important, why isn't Variant a field type? Yes Nulls are a part of RDBs, that doesn't mean you should use the largest variable imaginable to handle them.

The job of a DBA and Developer are by far different. DBA's job is to design the back end system and keep it up, as well as make sure the data is delivered/deliverable to the application and data is recieved/recievable from the the client. This includes security of the back end.

(sometimes you have a SQL developer between the DBA and the application developer)

The developer builds the application that interfaces with the objects the DBA has built. Whether they be tables, or more structured stored procedures, functions, and views.

Many of us span both jobs. In doing so we take bits and pieces from each and confuse where the line really is.

http://visualbasic.about.com/od/imhoinmyhumbleopinion/ss/FiveChanges_7.htm

Only VB uses variants to such extreme. If you're using .NET, you don't have the same luxury.

This arguement can be the same as implicit and explicit declarations...

I would say as a DBA perhaps I'm not the best with Access. I'm more familiar with MSSQL2k/2k3 w/ .Net. I use wrappers for almost everything to avoid nulls.

I use access for applications that need to be supported by people that are less technically inclined, want to make their own reports, and/or need someone without a technical background to build adhoc query/functionality.

My general practice is to use the same model for programming for all languages. The broad brush of "lazy" use of a variant, was used in a broad statement. not all uses are incorrect, but most are.

The variant data type as an argument has always left me uncomfortable. Same with the "object" data type. Advanced programming techniques such as late binding and overloading are mimic'd using non-explicit type casted variables.

My general statement was for the general user, not for all. It is imo a good practice to avoid large variables when not necessary.

But I do concede that they are necessary at times. I just believe they are more often used when they aren't needed.


Randall Vollen
National City Bank Corp.

 

[LARiot]

You have to use variant for arrays. Also if the parameter is delcared as variant you have less code and hence less usage of memory... and if my main frame days taught me anything it's to save memory. Ah, how I miss COBOL.

hwkranger, a million lines of code? Oh please, I'm sure it's more like 999,999.

 

[hwkranger]

I think my original point was misunderstood. I conceed that Variants are an important data type in VB/A/S.

Millions may have been a bit of an exageration...

Randall Vollen
Merrill Lynch