How to eliminate blank line

[JIM]

Hi, I'm using Access 2000, here's line used to eliminate blank line:
=IIf(Len([BillingAddress2])=0,"",Trim([City] & (", "+[State]+" ") &
IIf(Len([Zip])>5,Left([Zip],5) & "-" & Mid([Zip],6),[Zip]))).

If billingaddress2 is blank it's necessary to move City, State, Zip up one
line. This work great when it's blank. But when not blank it moves City,
State, Zip up anyway and clobbers BillingAddress2. What am I doing wrong?
Thank, JIM

 

[Rick B]

I think you are making it way to hard. Have you looked at the CAN GROW and
CAN SHRINK properties? make the fields one pixel high and set them to CAN
GROW.

 

[JIM]

Hi, thanks for reply. I can't use Can Shrink because there is so much
information on top of iinvoice that is needed and it's on same horizontal
line as address lines.
JIM

I think you are making it way to hard. Have you looked at the CAN GROW and
CAN SHRINK properties? make the fields one pixel high and set them to CAN
GROW.

--
Rick B

Hi, I'm using Access 2000, here's line used to eliminate blank line:
=IIf(Len([BillingAddress2])=0,"",Trim([City] & (", "+[State]+" ") &
IIf(Len([Zip])>5,Left([Zip],5) & "-" & Mid([Zip],6),[Zip]))).

If billingaddress2 is blank it's necessary to move City, State, Zip up one
line. This work great when it's blank. But when not blank it moves City,
State, Zip up anyway and clobbers BillingAddress2. What am I doing wrong?
Thank, JIM

 

[BruceM]

=IIf([BillingAddress2] Is Null,"",[BillingAddress2] & Chr(13) & Chr(10)) &
[City] & ", " & [State] & " " & [Zip]

Chr(13) and Chr(10) are carriage return and line feed (or maybe I have that
backward, but in any case they need to be in the given order). The logic is
that if [BillingAddress2] is empty, do nothing, otherwise add
[BillingAddress2] and go to the next line for City, State, and Zip.

It may also work to do something like: =([BillingAddress2] + Chr(13) +
Chr(10)) & [City] & ", " & [State] & " " & [Zip]. When the + sign is used
as the concatenation operator, if any of the values are null then the entire
expression will evaluate to null. I have heard some disagreement about
this, but from my experience it seems to work.

 

[JIM]

Thanks Bruce. I finally used the following based on your example:
=IIf([BillingAddress2] Is Null,"",[BillingAddress2] & Chr(13) & Chr(10)) &
[City] & ", " & [State] & " " & IIf(Len([Zip])>5,Left([Zip],5) & "-" &
Mid([Zip],6),[Zip])
For the life of me I can't figure how this works but it does! : )

And then for the next line:
=IIf([BillingAddress2] Is Null,"",[City] & ", " & [State] & " " &
IIf(Len([Zip])>5,Left([Zip],5) & "-" & Mid([Zip],6),[Zip]))
I do understand this one.
Thanks again, JIM

=IIf([BillingAddress2] Is Null,"",[BillingAddress2] & Chr(13) & Chr(10)) &
[City] & ", " & [State] & " " & [Zip]

Chr(13) and Chr(10) are carriage return and line feed (or maybe I have that
backward, but in any case they need to be in the given order). The logic is
that if [BillingAddress2] is empty, do nothing, otherwise add
[BillingAddress2] and go to the next line for City, State, and Zip.

It may also work to do something like: =([BillingAddress2] + Chr(13) +
Chr(10)) & [City] & ", " & [State] & " " & [Zip]. When the + sign is used
as the concatenation operator, if any of the values are null then the entire
expression will evaluate to null. I have heard some disagreement about
this, but from my experience it seems to work.

Hi, I'm using Access 2000, here's line used to eliminate blank line:
=IIf(Len([BillingAddress2])=0,"",Trim([City] & (", "+[State]+" ") &
IIf(Len([Zip])>5,Left([Zip],5) & "-" & Mid([Zip],6),[Zip]))).

If billingaddress2 is blank it's necessary to move City, State, Zip up one
line. This work great when it's blank. But when not blank it moves City,
State, Zip up anyway and clobbers BillingAddress2. What am I doing wrong?
Thank, JIM

 

[BruceM]

I gave you some incorrect information. I left out City, State, and Zip if
Address2 is blank. It should have been:

=IIf([BillingAddress2] Is Null,[City] & ", " & [State] & " " &
[Zip],[BillingAddress2] & Chr(13) & Chr(10)) & [City] & ", " & [State] & "
" & [Zip]

The whole thing can be handled in one expression.

The second example you gave leaves you with an empty string if
BillingAddress2 is null, and it leaves out BillingAddress2 if there is
something in the field. I don't see how that would work for you. Also, I
don't know what you mean by "the next line".

Regarding Zip codes, keep in mind that not all countries use the same
format, in case that matters in your situation. Otherwise you can just use
your code (Left, etc.) where I have used [Zip].

I think that using the + sign as the concatenation operator, as in my second
example, will save you some writing.

Thanks Bruce. I finally used the following based on your example:
=IIf([BillingAddress2] Is Null,"",[BillingAddress2] & Chr(13) & Chr(10)) &
[City] & ", " & [State] & " " & IIf(Len([Zip])>5,Left([Zip],5) & "-" &
Mid([Zip],6),[Zip])
For the life of me I can't figure how this works but it does! : )

And then for the next line:
=IIf([BillingAddress2] Is Null,"",[City] & ", " & [State] & " " &
IIf(Len([Zip])>5,Left([Zip],5) & "-" & Mid([Zip],6),[Zip]))
I do understand this one.
Thanks again, JIM

=IIf([BillingAddress2] Is Null,"",[BillingAddress2] & Chr(13) & Chr(10))
&
[City] & ", " & [State] & " " & [Zip]

Chr(13) and Chr(10) are carriage return and line feed (or maybe I have
that
backward, but in any case they need to be in the given order). The logic
is
that if [BillingAddress2] is empty, do nothing, otherwise add
[BillingAddress2] and go to the next line for City, State, and Zip.

It may also work to do something like: =([BillingAddress2] + Chr(13) +
Chr(10)) & [City] & ", " & [State] & " " & [Zip]. When the + sign is
used
as the concatenation operator, if any of the values are null then the
entire
expression will evaluate to null. I have heard some disagreement about
this, but from my experience it seems to work.

Hi, I'm using Access 2000, here's line used to eliminate blank line:
=IIf(Len([BillingAddress2])=0,"",Trim([City] & (", "+[State]+" ") &
IIf(Len([Zip])>5,Left([Zip],5) & "-" & Mid([Zip],6),[Zip]))).

If billingaddress2 is blank it's necessary to move City, State, Zip up
one
line. This work great when it's blank. But when not blank it moves
City,
State, Zip up anyway and clobbers BillingAddress2. What am I doing
wrong?
Thank, JIM