Help with IIF statement on Report 2

[gypsylady]

I am baffled. I'm on a report that comes from a query.
I want to only show the [Business] name on my form if there is no agent listed in [LName].
I entered: =Iif(IsNull([LName]),[Business]," &quot

This works in those instances where [LName] is not Null, but if it IsNull then I get : #Error.

I know that [Business] is valid because, alone it works.
Can anyone tell me what I am doing wrong? Appreciate any help you can offer.

 

[LonnieJohnson]

If the field property "Allow Zero Lenth" is set to No in the table for LNAME, then there may be no Nulls to detect. Try looking for blanks also.

=Iif(IsNull([LName] Or [LName] = "&quot,[Business]," &quot

B-)

ljprodev@yahoo.com
ProDev, MS Access Applications

http://www.galaxymall.com/software/PRODEV

 

[gypsylady]

Lonnie,
Thank you for the quick reply. Unfortunately, when I added the search for blanks, and I still get the same results.
Any other ideas?

 

[LonnieJohnson]

What exactly does the error say? ljprodev@yahoo.com
ProDev, MS Access Applications

http://www.galaxymall.com/software/PRODEV

 

[gypsylady]

in those instances where [LName] is blank or null, the business name is showing up as " #error ", instead of listing the name of the business.

 

[LonnieJohnson]

Can you email me the report, query and table? ljprodev@yahoo.com
ProDev, MS Access Applications

http://www.galaxymall.com/software/PRODEV

 

[gypsylady]

I'd be glad to. I'll send it right now.

 

[gypsylady]

Lonnie Johnson, you got me to send you my database, and then you never responded. that stinks!

 

[LonnieJohnson]

I never received it. Which email address did you send it to? I will be glad to take a look at it if you still need help. Was that you that left me a phone message? ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[JaneC]

Try changing the name of the control, on Other tab. If this is set to Business then this is probably the problem

JC

 

[LakotaMan]

I have had better luck using something like this for text analysis with IIF:

[lname]<>&quot;&quot;

which is reverse logic of what you're setting up, but works for me. Also, I don't use OR with IIF and seem to get it to work best with the IN operator.

vadam@pwlf.com

 

[Microbabble]

OK I'm a newbie but the syntax for an IIf is IIf not Iif. I've found Microsoft is pretty unforgiving when it come to syntax.

=Trim(IIf(IsNull([LName]),[Business],&quot; &quot)

The Trim function simply removes preceding spaces

=IIf(IsNull([LName]),[Business],&quot; &quot

That should work

 

[JoeMiller]

You can check the length of the string using len and make sure that the name of the text box is not LName OR Business:

=IIf(Len([LName])>0,[Business],&quot;&quot

HTH
Joe Miller
joe.miller@flotech.net

 

[RoyVidar]

Hi!

There are several &quot;challenges&quot; in this thread. One is the Isnull/len thing.

I've learned to use this for check on strings, and I haven't seen it fail yet.

IIF(Len(txtName & &quot;&quot>0;txtName;txtName2).

The next bit, is something JaneC mentioned, the name of the control. Try inserting a new textbox, give it a name that is not in use (txtBlaBla), where you add your comparision.

=Iif(Len([LName] & &quot;&quot,[Business],&quot; &quot

Cheers, Roy-Vidar

 

[gypsylady]

Thanks to all who replied. I tried them all.
I don't understand why, but JaneC's tip of changing the control to a name other than &quot;Business&quot; worked.