Help needed with control returning #Error?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rashar S - 17 Nov 2005 21:17 GMT

Hello,

I have a text box that when the query runs, it either returns a value, or
there may not be a value (empty). When the form is displayed, the textbox
reads #Error

Here is my script thus far that returns #Error in my textbox

=IIf([Products_last]<10,"",IIf(IsNull(nz([Products_This],1)*[Products_Last])
Or IsError([Products_This]*[Products_Last]),"",IIf(CLng([Products_Last]*0.8)-
1<11,10,"10-" & CLng([Products_Last]*0.8)-1)))

And this is what I have tried...which will return #Name

=IIf([Products_last] [isnull] OR [Products_last] <10,"",IIf(IsNull(nz(
[Products_This],1)*[Products_Last]) Or IsError([Products_This]*[Products_Last]
),"",IIf(CLng([Products_Last]*0.8)-1<11,10,"10-" & CLng([Products_Last]*0.8)-
1)))

Also.... returns #Name
=IIf([Products_last] [isempty] OR [Products_last] <10,"",IIf(IsNull(nz(
[Products_This],1)*[Products_Last]) Or IsError([Products_This]*[Products_Last]
),"",IIf(CLng([Products_Last]*0.8)-1<11,10,"10-" & CLng([Products_Last]*0.8)-
1)))

Any help is greatly appreciated.

Thanks,

Rashar

Reply to this Message

Rashar S - 17 Nov 2005 21:20 GMT

>Hello,
>
[quoted text clipped - 20 lines]
>),"",IIf(CLng([Products_Last]*0.8)-1<11,10,"10-" & CLng([Products_Last]*0.8)-
>1)))

*** What I wnat it to do is when the form loads, I if there is no results in
the textbox control, then I just want to have it blank... *****

>Any help is greatly appreciated.
>
>Thanks,
>
>Rashar

Reply to this Message

OfficeDev18 - 17 Nov 2005 22:11 GMT

=IIf([Products_last]<10,"",IIf(IsNull(nz([Products_This],1)*[Products_Last])
Or IsError([Products_This]*[Products_Last]),"",IIf(CLng([Products_Last]*0.8)-
1<11,10,"10-" & CLng([Products_Last]*0.8)-1)))

Wow! What a formula!

Having been wowed, there are some things needing fixing in the formula. For
one thing, once you're initializing Products_This to 1, doing an IsNull isn't
really interested in Products_this, because IsNull(Nz([Products_This],1))
will always give you False.

The 'Or' isn't necessary in a nested IIf() function.

Finally, even though your text box is variant, using the term 10 and also the
term ,"10-" is confusing. Ergo, change your formula as follows and try it now:

=IIf([Products_Last]<10,"",IIf(IsNull([Products_Last]),"",
IIf(IsError([Products_This]*[Products_Last]),"",IIf((CLng([Products_Last]*0.8)
-
1) <11,"10",CStr(10- (CLng([Products_Last]*0.8)-1))))))

Please note that I added some parentheses for clarity.

HTH

>Hello,
>
[quoted text clipped - 26 lines]
>
>Rashar

Signature

Sam

Reply to this Message

Rashar S - 18 Nov 2005 14:59 GMT

Ok...That worked well. I wasn't sure if I could have omit the OR in the
statement or not...

Much Thanks,

Rashar

>=IIf([Products_last]<10,"",IIf(IsNull(nz([Products_This],1)*[Products_Last])
>Or IsError([Products_This]*[Products_Last]),"",IIf(CLng([Products_Last]*0.8)-
[quoted text clipped - 26 lines]
>>
>>Rashar

Reply to this Message