error in function when field is null (1 Viewer)

[Leo_Polla_Psemata]

Hi
I am trying to create the below function.
I managed to bring the correct result, however, the field EDF, might contain data or zero or empty.
While with data or zero, i get the result i want, when empty, the function returns error.
Is there any way i could work it around ?

Public Function DemrCalc(DISF As Date, INME As Date, EDF As Long, sztp As String) As String

I have tried this but still i get error if EDF is empty

            If IsEmpty(EDF) Or EDF = 0 Then
                PA = 12
            Else
                PA = 12 + EDF
            End If

 

[Josef P.]

Public Function DemrCalc(DISF As Date, INME As Date, EDF As Long, sztp As String) As String

You cannot pass Null to a long parameter.

Variant 1: Variant instead of Long

Public Function DemrCalc(DISF As Date, INME As Date, EDF As Variant, sztp As String) As String
...
     if isnull(EDF) ....
     ' or
     PA = 12 + Nz(EDF, 0)
...

Variant 2: use Nz-Function when calling the function

Public Function DemrCalc(DISF As Date, INME As Date, EDF As Long, sztp As String) As String
...
'with call
DemrCalc DISFvalue, INMEValue, Nz(EDFvalue, 0), ...

BTW: Unless absolutely necessary, I would use ByVal instead of ByRef for the parameters.

 

[Pat Hartman]

ByVal doesn't help.
To have an optional argument, you must define the argument as optional. Defining it as a variant rather than long might work and allow you to pass a null.

 

[Josef P.]

ByVal doesn't help.

? Where does it say that ByVal should allow Null?

I suspect a similar call like that:

with aRecordsetWithFieldsForDemrCalc
...
      DemrCalc !DISF.Value, !INME.Value, !EDF.Value, !sztp.Value
....
end with

If !EDF.Value is null, the parameter with type Long will not work.
=>
variant 1 from #2: parameter: EDF As Variant + Nz(EDF, 0) or check with Isnull(EDF) inside function
or with variant 2 from #2:

with aRecordsetWithFieldsForDemrCalc
...
      DemrCalc !DISF.Value, !INME.Value, Nz(!EDF.Value, 0), !sztp.Value
....
end with

 

[Galaxiom]

Personally I would not use a UDF for such a trivial operation. It will slow down the query which would otherwise be processed by the engine.

use Nz-Function when calling the function

For the same reason it is far better to use Is Not Null than wrapping the argument in NZ() when calling a function that will not accept a Null.