using iff statement in a form

[lubbs65]

i am trying to use an iff statement in a form.
pretty much the statement would be in a savings textbox
i can type = [revenue]-[cost] and i get a result.

however, it has to be ok for [cost] to be blank. therefore, i've tried:

iif([cost] is NULL, [revenue], [revenue]-[cost])

this did not work. i wasn't sure if it was my syntax in "[cost] is NULL" so i tried:

iif([cost] =100, [revenue], [revenue]-[cost])

this still just gave me #name in the text box. What am I doing wrong?
I've been under the impression that I can simply just type that into the textbox in design view but clearly it has not been working.

 

[Norie]

Why not add that expression to the query the form is based on?

Savings:[Revenue] - Nz([cost], 0)

You could then use that as the control source for your savings textbox.

You could just enter it, with a = in from, in the Control Source property of the textbox.

 

[boblarson]

If you don't add it to the query, then you have to use the IIF properly for the control source. It should start with an = sign and then it is best if the controls involved in the calculation are not named the same as the fields that you have. Also you don't use Is Null in this case you would use:

=IIf (IsNull([cost]), Nz([revenue], 0), Nz([revenue],0) - Nz([cost], 0))

but that can be broken down to simply:

=Nz([revenue],0) - Nz([cost],0)

As it will provide the right answer regardless. So no IIF statement is actually needed.

 

[lubbs65]

thanks!
/thread

 

[Norie]

Bob

I was able to use the expression I posted in the control source without Iif.

 

[boblarson]

Bob

I was able to use the expression I posted in the control source without Iif.

Without the equals sign? That is what I had asked about, not the IIF (I had stated "To use the IIF PROPERLY..."). I know you can do it without the IIF as I put in my last post.

 

[lubbs65]

NVM figured it out (i had a 2nd question but i just discovered the answer).