Address box with IIf Statement

G

Guest

I'm looking for help with this statement. I have created an address text box
with the following control. If any of the lines are null then the line will
not display and go onto the next. It's working fine except the line with
city, state and zip.

If Address 2, 3 & 4, city, state & zip are blank. I want it to look like this:

My Name
Address 1
Country
(Blank carriage return)
Attn:

But I'm getting this:

My Name
Address 1
(Blank carriage return)
(Blank carriage return)
Country
Attn:

I can't figure out what's wrong with the statement. I want the City, State
and Zip to stay on one line. But, if City, State and Zip are null, I want the
line to close up.

Here is the code I have:

=Trim([BILL_TO_NAME] & Chr(13) & Chr(10) & [BILL_TO_ADR1] &
IIf(IsNull([BILL_TO_ADR1]),Null,Chr(13) & Chr(10)) & [BILL_TO_ADR2] &
IIf(IsNull([BILL_TO_ADR2]),Null,Chr(13) & Chr(10)) & [BILL_TO_ADR3] &
IIf(IsNull([BILL_TO_ADR3]),Null,Chr(13) & Chr(10)) & [BILL_TO_ADR4] &
IIf(IsNull([BILL_TO_ADR4]),Null,Chr(13) & Chr(10)) & RTrim([BILL_TO_CITY]) &
IIf(IsNull([BILL_TO_CITY]),Null," " & [BILL_TO_STATE] &
IIf(IsNull([BILL_TO_STATE]),Null," " & [BILL_TO_ZIP] &
IIf(IsNull([BILL_TO_ZIP]),Null,Chr(13) & Chr(10))) & [BILL_TO_CNTRY] &
IIf(IsNull([BILL_TO_CNTRY]),Null,Chr(13) & Chr(10)) & "ATTN: " &
[BILL_TO_ATTN]))
 
R

RBear3

You don't need all that.

Create a text box that is 1 or 2 pixels high.

Set it to CAN GROW.

Enter something like the following in it...

= [BILL_TO_ADR1] & chr(13) & chr(10) & ([BILL_TO_ADR2] + chr(13) & chr(10))
& ([BILL_TO_ADR2] + chr(13) & chr(10)) etc.








The plus (+) says that you only append the item (CR and LF) if the other
item is not null.
 
G

Guest

Thanks very much for the input, but my results have not changed. I even added
a Trim command, just in case there were blank characters to get rid of which
were keeping the extra lines open. Does anyone have an idea as to why this is
not working?

The code as I have it now is:

=Trim([BILL_TO_NAME]) & Chr(13) & Chr(10)
& Trim([BILL_TO_ADR1]) & Chr(13) & Chr(10)
& (Trim([BILL_TO_ADR2])+Chr(13) & Chr(10))
& (Trim([BILL_TO_ADR3])+Chr(13) & Chr(10))
& (Trim([BILL_TO_ADR4])+Chr(13) & Chr(10))
& (Trim([BILL_TO_CITY])+" ")
& (Trim([BILL_TO_STATE]) & " ")
& (Trim([BILL_TO_ZIP])+Chr(13) & Chr(10))
& (Trim([BILL_TO_CNTRY])+Chr(13) & Chr(10))
& Trim([BILL_TO_ATTN])

My results look like this: (with adr3, 4, city, state & zip are blank and
should close up)


My Name
Adrs1
Adr2
(carriage return) - don't want this! Adr3 is blank
(carriage return) - don't want this! Adr4 is blank
(carriage return) - don't want this! City, State & Zip are blank
Country
Attention

I'm still getting 8 lines all together, but it should close down to 5.

RBear3 said:
You don't need all that.

Create a text box that is 1 or 2 pixels high.

Set it to CAN GROW.

Enter something like the following in it...

= [BILL_TO_ADR1] & chr(13) & chr(10) & ([BILL_TO_ADR2] + chr(13) & chr(10))
& ([BILL_TO_ADR2] + chr(13) & chr(10)) etc.








The plus (+) says that you only append the item (CR and LF) if the other
item is not null.



--
Hope that helps!

RBear3
..

laknight said:
I'm looking for help with this statement. I have created an address text
box
with the following control. If any of the lines are null then the line
will
not display and go onto the next. It's working fine except the line with
city, state and zip.

If Address 2, 3 & 4, city, state & zip are blank. I want it to look like
this:

My Name
Address 1
Country
(Blank carriage return)
Attn:

But I'm getting this:

My Name
Address 1
(Blank carriage return)
(Blank carriage return)
Country
Attn:

I can't figure out what's wrong with the statement. I want the City, State
and Zip to stay on one line. But, if City, State and Zip are null, I want
the
line to close up.

Here is the code I have:

=Trim([BILL_TO_NAME] & Chr(13) & Chr(10) & [BILL_TO_ADR1] &
IIf(IsNull([BILL_TO_ADR1]),Null,Chr(13) & Chr(10)) & [BILL_TO_ADR2] &
IIf(IsNull([BILL_TO_ADR2]),Null,Chr(13) & Chr(10)) & [BILL_TO_ADR3] &
IIf(IsNull([BILL_TO_ADR3]),Null,Chr(13) & Chr(10)) & [BILL_TO_ADR4] &
IIf(IsNull([BILL_TO_ADR4]),Null,Chr(13) & Chr(10)) & RTrim([BILL_TO_CITY])
&
IIf(IsNull([BILL_TO_CITY]),Null," " & [BILL_TO_STATE] &
IIf(IsNull([BILL_TO_STATE]),Null," " & [BILL_TO_ZIP] &
IIf(IsNull([BILL_TO_ZIP]),Null,Chr(13) & Chr(10))) & [BILL_TO_CNTRY] &
IIf(IsNull([BILL_TO_CNTRY]),Null,Chr(13) & Chr(10)) & "ATTN: " &
[BILL_TO_ATTN]))
Click to expand...
Click to expand...
 
G

Guest

I also tried this statement and Access said it was too complex:

=[BILL_TO_NAME] & IIf((IsNull([BILL_TO_ADR1]) Or
[BILL_TO_ADR1]=""),"",[chr(13)+chr(10) & BILL_TO_ADR1] &
IIf((IsNull([BILL_TO_ADR2]) Or [BILL_TO_ADR2]=""),"",[chr(13)+chr(10) &
BILL_TO_ADR2] &
IIf((IsNull([BILL_TO_ADR3]) Or [BILL_TO_ADR3]=""),"",[chr(13)+chr(10) &
BILL_TO_ADR3] &
IIf((IsNull([BILL_TO_ADR4]) Or [BILL_TO_ADR4]=""),"",[chr(13)+chr(10) &
BILL_TO_ADR4] &
IIf((IsNull([BILL_TO_ADR4]) Or [BILL_TO_ADR4]=""),"",[chr(13)+chr(10) &
BILL_TO_ADR4] &
IIf((IsNull([BILL_TO_CITY] Or [BILL_TO_CITY]=""),"",[BILL_TO_CITY]+" " &
IIf((IsNull([BILL_TO_STATE] Or [BILL_TO_STATE]=""),"",[BILL_TO_STATE]+" " &
IIf((IsNull([BILL_TO_ZIP] Or [BILL_TO_ZIP]=""),"",[chr(13)+chr(10) &
BILL_TO_CNTRY] &
IIf((IsNull([BILL_TO_CNTRY] Or [BILL_TO_CNTRY]=""),"",[chr(13)+chr(10) +
chr(13) + chr(10) & BILL_TO_ATTN]
 
R

RBear3

I have not tested it, but maybe try...

=Trim([BILL_TO_NAME]) & Chr(13) & Chr(10)
& Trim([BILL_TO_ADR1]) & Chr(13) & Chr(10)
& (Trim([BILL_TO_ADR2])+Chr(13) + Chr(10))
& (Trim([BILL_TO_ADR3])+Chr(13) + Chr(10))
& (Trim([BILL_TO_ADR4])+Chr(13) + Chr(10))
& (Trim([BILL_TO_CITY])+" ")
& (Trim([BILL_TO_STATE]) + " ")
& (Trim([BILL_TO_ZIP])+Chr(13) + Chr(10))
& (Trim([BILL_TO_CNTRY])+Chr(13) + Chr(10))
& Trim([BILL_TO_ATTN])

--
Hope that helps!

RBear3
..

laknight said:
Thanks very much for the input, but my results have not changed. I even
added
a Trim command, just in case there were blank characters to get rid of
which
were keeping the extra lines open. Does anyone have an idea as to why this
is
not working?

The code as I have it now is:

=Trim([BILL_TO_NAME]) & Chr(13) & Chr(10)
& Trim([BILL_TO_ADR1]) & Chr(13) & Chr(10)
& (Trim([BILL_TO_ADR2])+Chr(13) & Chr(10))
& (Trim([BILL_TO_ADR3])+Chr(13) & Chr(10))
& (Trim([BILL_TO_ADR4])+Chr(13) & Chr(10))
& (Trim([BILL_TO_CITY])+" ")
& (Trim([BILL_TO_STATE]) & " ")
& (Trim([BILL_TO_ZIP])+Chr(13) & Chr(10))
& (Trim([BILL_TO_CNTRY])+Chr(13) & Chr(10))
& Trim([BILL_TO_ATTN])

My results look like this: (with adr3, 4, city, state & zip are blank and
should close up)


My Name
Adrs1
Adr2
(carriage return) - don't want this! Adr3 is blank
(carriage return) - don't want this! Adr4 is blank
(carriage return) - don't want this! City, State & Zip are blank
Country
Attention

I'm still getting 8 lines all together, but it should close down to 5.

RBear3 said:
You don't need all that.

Create a text box that is 1 or 2 pixels high.

Set it to CAN GROW.

Enter something like the following in it...

= [BILL_TO_ADR1] & chr(13) & chr(10) & ([BILL_TO_ADR2] + chr(13) &
chr(10))
& ([BILL_TO_ADR2] + chr(13) & chr(10)) etc.








The plus (+) says that you only append the item (CR and LF) if the other
item is not null.



--
Hope that helps!

RBear3
..

laknight said:
I'm looking for help with this statement. I have created an address
text
box
with the following control. If any of the lines are null then the line
will
not display and go onto the next. It's working fine except the line
with
city, state and zip.

If Address 2, 3 & 4, city, state & zip are blank. I want it to look
like
this:

My Name
Address 1
Country
(Blank carriage return)
Attn:

But I'm getting this:

My Name
Address 1
(Blank carriage return)
(Blank carriage return)
Country
Attn:

I can't figure out what's wrong with the statement. I want the City,
State
and Zip to stay on one line. But, if City, State and Zip are null, I
want
the
line to close up.

Here is the code I have:

=Trim([BILL_TO_NAME] & Chr(13) & Chr(10) & [BILL_TO_ADR1] &
IIf(IsNull([BILL_TO_ADR1]),Null,Chr(13) & Chr(10)) & [BILL_TO_ADR2] &
IIf(IsNull([BILL_TO_ADR2]),Null,Chr(13) & Chr(10)) & [BILL_TO_ADR3] &
IIf(IsNull([BILL_TO_ADR3]),Null,Chr(13) & Chr(10)) & [BILL_TO_ADR4] &
IIf(IsNull([BILL_TO_ADR4]),Null,Chr(13) & Chr(10)) &
RTrim([BILL_TO_CITY])
&
IIf(IsNull([BILL_TO_CITY]),Null," " & [BILL_TO_STATE] &
IIf(IsNull([BILL_TO_STATE]),Null," " & [BILL_TO_ZIP] &
IIf(IsNull([BILL_TO_ZIP]),Null,Chr(13) & Chr(10))) & [BILL_TO_CNTRY] &
IIf(IsNull([BILL_TO_CNTRY]),Null,Chr(13) & Chr(10)) & "ATTN: " &
[BILL_TO_ATTN]))
Click to expand...
Click to expand...
Click to expand...
 
R

RBear3

Why don't you try a simpler one and see if it works...

=([BILL_TO_ADR1] + chr(13) + chr(10)) & (BILL_TO_ADR2] + chr(13) + chr(10))



Does that do it? Is [BII_TO_ADR2] actually null in some cases, or do you
have other characters in there?
 
J

John Spencer

Possibly the values are not null but are zero length strings or even
multi-length strings containing only spaces.

=[BILL_TO_NAME])
& IIF(Len([BILL_TO_ADR1] & "") = 0 ,"", Chr(13) & Chr(10) & BILL_To_Adr1)
& IIF(Len([BILL_TO_ADR2] & "") = 0 ,"", Chr(13) & Chr(10) & BILL_To_Adr2)
& IIF(Len([BILL_TO_ADR3] & "") = 0 ,"", Chr(13) & Chr(10) & BILL_To_Adr3)
& IIF(Len([BILL_TO_ADR4] & "") = 0 ,"", Chr(13) & Chr(10) & BILL_To_Adr4)
& IIF(Len([BILL_TO_CITY] & "") = 0, "", [BILL_TO_CITY] & ", ")
& IIF(Len([BILL_TO_STATE] & "") =0,"",BILL_TO_STATE & " ")
& IIF(Len([BILL_TO_ZIP] & "")=0, "", BILL_TO_ZIP)
& IIF (Len([BILL_TO_CNTRY] & "")=0,"", Chr(13) & Chr(10) & BILL_TO_COUNTRY)
& IIF(Len([BILL_TO_ATTN]) &"")=0,"",Chr(13) & Chr(10) & BILL_TO_ATTN)

If your fields contain multiple space characters then you might need to
change the test to
IIF(LEN(Trim(SomeField & "")) = 0, "", ....


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

laknight said:
Thanks very much for the input, but my results have not changed. I even
added
a Trim command, just in case there were blank characters to get rid of
which
were keeping the extra lines open. Does anyone have an idea as to why this
is
not working?

The code as I have it now is:

=Trim([BILL_TO_NAME]) & Chr(13) & Chr(10)
& Trim([BILL_TO_ADR1]) & Chr(13) & Chr(10)
& (Trim([BILL_TO_ADR2])+Chr(13) & Chr(10))
& (Trim([BILL_TO_ADR3])+Chr(13) & Chr(10))
& (Trim([BILL_TO_ADR4])+Chr(13) & Chr(10))
& (Trim([BILL_TO_CITY])+" ")
& (Trim([BILL_TO_STATE]) & " ")
& (Trim([BILL_TO_ZIP])+Chr(13) & Chr(10))
& (Trim([BILL_TO_CNTRY])+Chr(13) & Chr(10))
& Trim([BILL_TO_ATTN])

My results look like this: (with adr3, 4, city, state & zip are blank and
should close up)


My Name
Adrs1
Adr2
(carriage return) - don't want this! Adr3 is blank
(carriage return) - don't want this! Adr4 is blank
(carriage return) - don't want this! City, State & Zip are blank
Country
Attention

I'm still getting 8 lines all together, but it should close down to 5.

RBear3 said:
You don't need all that.

Create a text box that is 1 or 2 pixels high.

Set it to CAN GROW.

Enter something like the following in it...

= [BILL_TO_ADR1] & chr(13) & chr(10) & ([BILL_TO_ADR2] + chr(13) &
chr(10))
& ([BILL_TO_ADR2] + chr(13) & chr(10)) etc.








The plus (+) says that you only append the item (CR and LF) if the other
item is not null.



--
Hope that helps!

RBear3
..

laknight said:
I'm looking for help with this statement. I have created an address
text
box
with the following control. If any of the lines are null then the line
will
not display and go onto the next. It's working fine except the line
with
city, state and zip.

If Address 2, 3 & 4, city, state & zip are blank. I want it to look
like
this:

My Name
Address 1
Country
(Blank carriage return)
Attn:

But I'm getting this:

My Name
Address 1
(Blank carriage return)
(Blank carriage return)
Country
Attn:

I can't figure out what's wrong with the statement. I want the City,
State
and Zip to stay on one line. But, if City, State and Zip are null, I
want
the
line to close up.

Here is the code I have:

=Trim([BILL_TO_NAME] & Chr(13) & Chr(10) & [BILL_TO_ADR1] &
IIf(IsNull([BILL_TO_ADR1]),Null,Chr(13) & Chr(10)) & [BILL_TO_ADR2] &
IIf(IsNull([BILL_TO_ADR2]),Null,Chr(13) & Chr(10)) & [BILL_TO_ADR3] &
IIf(IsNull([BILL_TO_ADR3]),Null,Chr(13) & Chr(10)) & [BILL_TO_ADR4] &
IIf(IsNull([BILL_TO_ADR4]),Null,Chr(13) & Chr(10)) &
RTrim([BILL_TO_CITY])
&
IIf(IsNull([BILL_TO_CITY]),Null," " & [BILL_TO_STATE] &
IIf(IsNull([BILL_TO_STATE]),Null," " & [BILL_TO_ZIP] &
IIf(IsNull([BILL_TO_ZIP]),Null,Chr(13) & Chr(10))) & [BILL_TO_CNTRY] &
IIf(IsNull([BILL_TO_CNTRY]),Null,Chr(13) & Chr(10)) & "ATTN: " &
[BILL_TO_ATTN]))
Click to expand...
Click to expand...
Click to expand...
 
C

Chuck

Thanks very much for the input, but my results have not changed. I even added
a Trim command, just in case there were blank characters to get rid of which
were keeping the extra lines open. Does anyone have an idea as to why this is
not working?

The code as I have it now is:

=Trim([BILL_TO_NAME]) & Chr(13) & Chr(10)
& Trim([BILL_TO_ADR1]) & Chr(13) & Chr(10)
& (Trim([BILL_TO_ADR2])+Chr(13) & Chr(10))
& (Trim([BILL_TO_ADR3])+Chr(13) & Chr(10))
& (Trim([BILL_TO_ADR4])+Chr(13) & Chr(10))
& (Trim([BILL_TO_CITY])+" ")
& (Trim([BILL_TO_STATE]) & " ")
& (Trim([BILL_TO_ZIP])+Chr(13) & Chr(10))
& (Trim([BILL_TO_CNTRY])+Chr(13) & Chr(10))
& Trim([BILL_TO_ATTN])

My results look like this: (with adr3, 4, city, state & zip are blank and
should close up)


My Name
Adrs1
Adr2
(carriage return) - don't want this! Adr3 is blank
(carriage return) - don't want this! Adr4 is blank
(carriage return) - don't want this! City, State & Zip are blank
Country
Attention

I'm still getting 8 lines all together, but it should close down to 5.
Click to expand...
Maybe I don't understand the situation, but I would use 8 separate text boxes.
ie: Trim([BILL_TO_NAME]). No chr() codes.
Set all text boxes to can shrink, can grow to yes.
Text boxes with no data should shrink to zero leaving no empty rows in the
final print out.

Just a wizard prodder
Chuck
--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

P.O. Box and concatenation hiccup 2
Carriage Return in calculated field 7
Trouble with chr(13) & chr(10) in IIF statement 3
omitting carriage returns in mailing labels 2
Blank Lines in Report 1
Problems with carriage return in address field 5
Iff in address block 1
Same expression gets different results in Report and Query 1

Top