Address box with IIf Statement

  • Thread starter Thread starter Guest
  • Start date Start date
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]))
 
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.
 
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...
 
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]
 
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...
 
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?
 
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...
 
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
--
 
Back
Top