IIF Statement with address

[Guest]

I have a bunch of fileds. SOme are blank. I have the grasp of the IIF Statement, but am not sure I am using it properly in this instance:

Name1
Name2
Address1
Address2
Address3
City
State
Zip

Here are the codes i have going on....
=Name1
=iif([name2]>" ",[name2],[Address1])
=iif([address2]>" ",[address2],iif([address3]>" ",trim([city]&", "&[state]&" "&[zip]," "))
=iif([address2]>" ",[address2],iif([address3]>" ",trim([city]&", "&[state]&" "&[zip]," "))
..........

I have tried all I that is in my ability and I am just frustrating myself.

Any assistance would be GREATLY apprecitated.

THANKS!!!

David

 

[Marshall Barton]

I have a bunch of fileds. SOme are blank. I have the grasp of the IIF Statement, but am not sure I am using it properly in this instance:

Name1
Name2
Address1
Address2
Address3
City
State
Zip

Here are the codes i have going on....
=Name1
=iif([name2]>" ",[name2],[Address1])
=iif([address2]>" ",[address2],iif([address3]>" ",trim([city]&", "&[state]&" "&[zip]," "))
=iif([address2]>" ",[address2],iif([address3]>" ",trim([city]&", "&[state]&" "&[zip]," "))
.........

I have tried all I that is in my ability and I am just frustrating myself.

I understand your frustration, but I'm not sure I can figure
out what you're trying to achieve. The typical thing people
want to do with addresses that might not have a value in
every field is to squeeze out the blank lines.

If your situation allows, the easiest thing to do is to just
use four(? or five? or six?) text boxes bound to their
field:
Name1
Name2
Address1
Address2
Address3
=City & ", " & State & " " & Zip

and set each text box as well as the section's CanShrink
property to Yes.

If you can't use that approach because you have other
controls that get in the way, then (as long as the missing
values are NULL, not a space, not a zero length string, but
NULL) you can use a single text box with a tricky expression
that relies on the & operator ignoring nulls and the +
operator propagating nulls:

=Name1 & (Chr(13) + Chr(10) + Name2) & (Chr(13) + Chr(10) +
Address1) & (Chr(13) + Chr(10) + Address2) & (Chr(13) +
Chr(10) + Address3) & Chr(13) & Chr(10) City & ", " & State
& " " & Zip

If that's not what you want, post back with some sample
output or a description of what you want to happen when some
of the fields are Null(?).

 

[Larry Linson]

Simply create a Text Box for each of the Fields and set the Text Box's Can
Shrink property to Yes. If there is nothing alongside it, it will shrink
away to nothing if the Field is Null.

You don't need the elaborate code.

Larry Linson
Microsoft Access MVP

I have a bunch of fileds. SOme are blank. I have the grasp of the IIF

Statement, but am not sure I am using it properly in this instance:

Name1
Name2
Address1
Address2
Address3
City
State
Zip

Here are the codes i have going on....
=Name1
=iif([name2]>" ",[name2],[Address1])
=iif([address2]>" ",[address2],iif([address3]>" ",trim([city]&", "&[state]&" "&[zip]," "))
=iif([address2]>" ",[address2],iif([address3]>" ",trim([city]&", "&[state]&" "&[zip]," "))
........

I have tried all I that is in my ability and I am just frustrating myself.

Any assistance would be GREATLY apprecitated.

THANKS!!!

David