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

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

Problem Solved. Thanks

"Einar" wrote:

> 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

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

"Bruce" wrote:

> 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