Help needed with control returning #Error?

[Rashar S via AccessMonster.com]

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

 

[Rashar S via AccessMonster.com]

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)))

*** 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... *****

 

[OfficeDev18 via AccessMonster.com]

=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,

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

 

[Rashar S via AccessMonster.com]

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)-
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