Dealing with the dreaded NULL

[WineSnob]

I HATE NULL - How do I deal with a null value in a function that requires the argument? I want the function to run even if any of the arguments are null. I am using the function in several queries. Below is an example of the the query. Here is the function:

Public Function fnCalcIncome(nYear As Integer, ClientMonthlyInc As Currency, InflationFactor As Double) As Currency
Dim I As Integer
Dim IncludeInfl As Integer


For I = 1 To nYear
If I = 1 Then
StartIncomeAmt = ClientMonthlyInc



Else
StartIncomeAmt = (ClientMonthlyInc * (1 + InflationFactor) ^ (I - 1))

End If


fnCalcIncome = StartIncomeAmt

Next I


End Function

___________________________________________________________

Here is a field in the query where I need to use the function if as an Example [External2Amt] is null

ExtIncSeg1Y1: fnCalcIncome(1,[External1Amt],[External1InflFactor])+fnCalcIncome(1,[External2Amt],[External2InflFactor])

 

[DJkarl]

Wrap your arguments using the NZ function.

so [External2Amt] would become NZ([External2Amt]), optionally you can specify your own default value NZ([External2Amt],-1).

 

[WineSnob]

Thanks. That works, but I have 10 External Amounts so the query field will be huge.
Is there a way to do the same thing in the function to set any null arguments to zero?

 

[DJkarl]

Each argument has to be handled since numeric types don't allow nulls, you could change your argument types to variant then check the arguments in your function to see if it IsNull or IsNumeric.