getting many Yes/No controls to shrink

[JIM]

Using A2000 I'm trying to assign a value to an unbound textbox in order to
use the can shrink property but I get an #Error when I use this method:

=IIf([TwoInv]=False And [L&M]=False And [PO]=False And [SHNotes]=False,"Spec
Hand: 2Inv " & [TwoInv] & " LM " & [L&M] & " PO " & [PO],Null)

TwoInv, L&M, and PO are yes/no fields; SHNotes is a memo field. The plan
was to make the whole line Null unless there was something in any of the
fields. Thinking about it I realize I can't print out a yes/no field but I
don't know how to approach.
Thanks

 

[Marshall Barton]

Using A2000 I'm trying to assign a value to an unbound textbox in order to
use the can shrink property but I get an #Error when I use this method:

=IIf([TwoInv]=False And [L&M]=False And [PO]=False And [SHNotes]=False,"Spec
Hand: 2Inv " & [TwoInv] & " LM " & [L&M] & " PO " & [PO],Null)

TwoInv, L&M, and PO are yes/no fields; SHNotes is a memo field. The plan
was to make the whole line Null unless there was something in any of the
fields. Thinking about it I realize I can't print out a yes/no field but I
don't know how to approach.

A memo field will never be False so that check needs to ne
changed. You also have the last two arguments of the IIf
function backwards so you only display the case where they
are all false.

I think(?) you want something more like:

=IIf([TwoInv]=False And [L&M]=False And [PO]=False And
Len([SHNotes])=0, Null,"Spec Hand: 2Inv " & [TwoInv] & "
LM " & [L&M] & " PO " & [PO])

Another reason for #Error is if the text box is named the
same as any of the fields in the expression.

 

[JIM]

Thank you Marshall, yes I did have my logic wrong in IIf statement. The
other was just a careless mistake, I meant [SHNotes] = Null.
For those interested this is how problem was resolved: The source for my
report was a select query joining two files and there wasn't always a special
handling record for every account so that the yes/no fields in that case were
null. So I updated the 3 yes/no fields in SQL view in query to be:
Val(Nz[TwoInv],0) As UpdTwoInv (to force a numeric 0)

Then the report line label is printed by a unbound text box only if all
fields and not false or null:
=IIf([UpdTwoInv]=False And [UpdLM]=False And [UpdPO]=False And [SHNotes] Is
Null,Null,"Spec Hand:")
and each individual field is only printed if true:
=IIf([UpdTwoInv]=True,"2 Inv X ",Null
and the report section and each text box are assigned as can shrink - yes.
It works perfect now and if there are no special handling requirements the
line disappears from that account page.
Thanks again Marshall for putting me on the right track with the logic. It
made me look at my data.
JIM

Using A2000 I'm trying to assign a value to an unbound textbox in order to
use the can shrink property but I get an #Error when I use this method:

=IIf([TwoInv]=False And [L&M]=False And [PO]=False And [SHNotes]=False,"Spec
Hand: 2Inv " & [TwoInv] & " LM " & [L&M] & " PO " & [PO],Null)

TwoInv, L&M, and PO are yes/no fields; SHNotes is a memo field. The plan
was to make the whole line Null unless there was something in any of the
fields. Thinking about it I realize I can't print out a yes/no field but I
don't know how to approach.

A memo field will never be False so that check needs to ne
changed. You also have the last two arguments of the IIf
function backwards so you only display the case where they
are all false.

I think(?) you want something more like:

=IIf([TwoInv]=False And [L&M]=False And [PO]=False And
Len([SHNotes])=0, Null,"Spec Hand: 2Inv " & [TwoInv] & "
LM " & [L&M] & " PO " & [PO])

Another reason for #Error is if the text box is named the
same as any of the fields in the expression.