Check function parameters for null Values

[Guest]

I have written a function to dosomething and return the value to a text box
on my form. I want to pass 2 parameters from my form, i.e. cb_Product_1 and
cb_Product_2 but only if they are not null.

The following example in mytest1 worksbut requires hard coding the controls
in the code. I really need to specify parameters like in the second example
myTest2, but it gives me #error if I enter =
mytest2([cb_Product_1],[cb_SourcePlant_1]) as the formula in my textbox if
one or both of the parameters is null. When the statement is false (i.e. No
nulls), then it returns "Ok" which it should.

Why does mytest2 generate an error when the if statment is true?

Bruce....


Function mytest1()

a = [Forms]![frm_Price_Scenario]![cb_Product_1]
b = [Forms]![frm_Price_Scenario]![cb_SourcePlant_1]

If Nz(a) = "" Or Nz(b) = "" Then
mytest1 = ("Null")
Else
mytest1 = ("Ok")
End If

End Function




Function mytest2(a As String, b As Integer)

If Nz(a) = "" Or Nz(b) = "" Then
mytest2 = ("Null")
Else
mytest2 = ("Ok")
End If

End Function

 

[Guest]

Hi,

I think that you cannot pass NULL-values into the function where strings are
expected (normally generates Runtime Error 94). Try to change the string
datatype to Variant instead and then do the same test.

Einar

"Bruce" skrev:

 

[Guest]

Problem Solved. Thanks

Hi,

I think that you cannot pass NULL-values into the function where strings are
expected (normally generates Runtime Error 94). Try to change the string
datatype to Variant instead and then do the same test.

Einar

"Bruce" skrev:

I have written a function to dosomething and return the value to a text box
on my form. I want to pass 2 parameters from my form, i.e. cb_Product_1 and
cb_Product_2 but only if they are not null.

The following example in mytest1 worksbut requires hard coding the controls
in the code. I really need to specify parameters like in the second example
myTest2, but it gives me #error if I enter =
mytest2([cb_Product_1],[cb_SourcePlant_1]) as the formula in my textbox if
one or both of the parameters is null. When the statement is false (i.e. No
nulls), then it returns "Ok" which it should.

Why does mytest2 generate an error when the if statment is true?

Bruce....


Function mytest1()

a = [Forms]![frm_Price_Scenario]![cb_Product_1]
b = [Forms]![frm_Price_Scenario]![cb_SourcePlant_1]

If Nz(a) = "" Or Nz(b) = "" Then
mytest1 = ("Null")
Else
mytest1 = ("Ok")
End If

End Function




Function mytest2(a As String, b As Integer)

If Nz(a) = "" Or Nz(b) = "" Then
mytest2 = ("Null")
Else
mytest2 = ("Ok")
End If

End Function

 

[Guest]

strReturn = mytest1(Nz([Forms]![frm_Price_Scenario]![cb_Product_1],""), _
& Nz([Forms]![frm_Price_Scenario]![cb_SourcePlant_1],""))

Function mytest1(a As string, b As String) As String

mytest1 = IIf(a="" or b="","Null", "Ok")

End Function