469,626 Members | 853 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

No Blank Lines in Address Field of Report

MX1
Please help if you can. I have a form with an address field. There are 6
fields:

Name1
Name2
Address1
Address2
CityStateZip
Country

Ideally, I'd like the code to support never giving a blank line for an empty
address field. I think I've got it except for Address2. I cannot get it to
suppress the blank line even if Address2 is blank. Any advise would be
great. I've been going at this with trial and error for hours. Here's the
code:

=[Name1] & Chr(13) & Chr(10) &
IIf([Name2]="","",[Name2] & Chr(13) & Chr(10)) &
IIf([Address1]="","",[Address1] & Chr(13) & Chr(10)) &
IIf([Address2]="","",[Address2] & Chr(13) & Chr(10)) &
IIf([CityStateZip]="","",[CityStateZip] & Chr(13) & Chr(10)) &
IIf([Country]="","",[Country])
Nov 12 '05 #1
6 2490
MX1
Seems like this works except ADDRESS1 must be filled in. Doesn't suppress
it. I can't figure out why. F*%$ It!

=([Name1] & Chr(13) & Chr(10)) & (IIf([Name2]="","",[Name2] & Chr(13) &
Chr(10))) & (IIf([Address1]="","",[Address1] & Chr(13) & Chr(10))) &
(IIf(isnull([Address2]),"",[Address2] & Chr(13) & Chr(10))) &
(IIf([CityStateZip]="","",[CityStateZip] & Chr(13) & Chr(10))) &
(IIf([Country]="","",[Country]))

"MX1" <mx*@mx1.abc> wrote in message news:sQILb.10011$nt4.19309@attbi_s51...
Please help if you can. I have a form with an address field. There are 6
fields:

Name1
Name2
Address1
Address2
CityStateZip
Country

Ideally, I'd like the code to support never giving a blank line for an empty address field. I think I've got it except for Address2. I cannot get it to suppress the blank line even if Address2 is blank. Any advise would be
great. I've been going at this with trial and error for hours. Here's the code:

=[Name1] & Chr(13) & Chr(10) &
IIf([Name2]="","",[Name2] & Chr(13) & Chr(10)) &
IIf([Address1]="","",[Address1] & Chr(13) & Chr(10)) &
IIf([Address2]="","",[Address2] & Chr(13) & Chr(10)) &
IIf([CityStateZip]="","",[CityStateZip] & Chr(13) & Chr(10)) &
IIf([Country]="","",[Country])

Nov 12 '05 #2
"MX1" <mx*@mx1.abc> wrote in news:sQILb.10011$nt4.19309@attbi_s51:
Please help if you can. I have a form with an address field. There are
6 fields:

Name1
Name2
Address1
Address2
CityStateZip
Country

Ideally, I'd like the code to support never giving a blank line for an
empty address field. I think I've got it except for Address2. I cannot
get it to suppress the blank line even if Address2 is blank. Any advise
would be great. I've been going at this with trial and error for hours.
Here's the code:

=[Name1] & Chr(13) & Chr(10) &
IIf([Name2]="","",[Name2] & Chr(13) & Chr(10)) &
IIf([Address1]="","",[Address1] & Chr(13) & Chr(10)) &
IIf([Address2]="","",[Address2] & Chr(13) & Chr(10)) &
IIf([CityStateZip]="","",[CityStateZip] & Chr(13) & Chr(10)) &
IIf([Country]="","",[Country])


maybe ...

Function GetRidofAdjacentLineFeeds(ByVal s As String) As String
GetRidofAdjacentLineFeeds = Replace(s, vbNewLine & vbNewLine,
vbNewLine)
If InStr(s, vbNewLine & vbNewLine) = 0 Then
Exit Function
Else
GetRidofAdjacentLineFeeds = GetRidofAdjacentLineFeeds
(GetRidofAdjacentLineFeeds)
End If
End Function

assumption here is that you have taken nulls into account.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #3

"MX1" <mx*@mx1.abc> wrote in message news:sQILb.10011$nt4.19309@attbi_s51...
Please help if you can. I have a form with an address field. There are 6
fields:

Name1
Name2
Address1
Address2
CityStateZip
Country

Ideally, I'd like the code to support never giving a blank line for an empty address field. I think I've got it except for Address2. I cannot get it to suppress the blank line even if Address2 is blank. Any advise would be
great. I've been going at this with trial and error for hours. Here's the code:

=[Name1] & Chr(13) & Chr(10) &
IIf([Name2]="","",[Name2] & Chr(13) & Chr(10)) &
IIf([Address1]="","",[Address1] & Chr(13) & Chr(10)) &
IIf([Address2]="","",[Address2] & Chr(13) & Chr(10)) &
IIf([CityStateZip]="","",[CityStateZip] & Chr(13) & Chr(10)) &
IIf([Country]="","",[Country])

Nov 12 '05 #4
If this were in a Report, you might not need to concatenate the fields --
you could print each in a separate Text Box with its CanShrink property set
to Yes.

But, because an empty field in an Access table would be Null, you could use:

= Name1 & (Chr$(13) + Chr$(10) + Name2) &
(Chr$(13) + Chr$(10) + Address1) &
(Chr$(13) + Chr$(10) + Address2) &
(Chr$(13) + Chr$(10) + CityStateZip) &
(Chr$(13) + Chr$(10) + Country)

The carriage return and line feed will "drop out" for any field that is
Null, because

anything + Null gives Null

whereas

something & Null is something.

I know that Lyle knows this, so I have to suppose that he was just having a
little fun with you.

Larry Linson
Microsoft Access MVP

"MX1" <mx*@mx1.abc> wrote in message news:sQILb.10011$nt4.19309@attbi_s51...
Please help if you can. I have a form with an address field. There are 6
fields:

Name1
Name2
Address1
Address2
CityStateZip
Country

Ideally, I'd like the code to support never giving a blank line for an empty address field. I think I've got it except for Address2. I cannot get it to suppress the blank line even if Address2 is blank. Any advise would be
great. I've been going at this with trial and error for hours. Here's the code:

=[Name1] & Chr(13) & Chr(10) &
IIf([Name2]="","",[Name2] & Chr(13) & Chr(10)) &
IIf([Address1]="","",[Address1] & Chr(13) & Chr(10)) &
IIf([Address2]="","",[Address2] & Chr(13) & Chr(10)) &
IIf([CityStateZip]="","",[CityStateZip] & Chr(13) & Chr(10)) &
IIf([Country]="","",[Country])

Nov 12 '05 #5
MX1
Over my head, but thanks for the feedback. I was hoping to stick with the
structure I have and perhaps reorder the IIFs. :(
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
"MX1" <mx*@mx1.abc> wrote in news:sQILb.10011$nt4.19309@attbi_s51:
Please help if you can. I have a form with an address field. There are
6 fields:

Name1
Name2
Address1
Address2
CityStateZip
Country

Ideally, I'd like the code to support never giving a blank line for an
empty address field. I think I've got it except for Address2. I cannot
get it to suppress the blank line even if Address2 is blank. Any advise
would be great. I've been going at this with trial and error for hours.
Here's the code:

=[Name1] & Chr(13) & Chr(10) &
IIf([Name2]="","",[Name2] & Chr(13) & Chr(10)) &
IIf([Address1]="","",[Address1] & Chr(13) & Chr(10)) &
IIf([Address2]="","",[Address2] & Chr(13) & Chr(10)) &
IIf([CityStateZip]="","",[CityStateZip] & Chr(13) & Chr(10)) &
IIf([Country]="","",[Country])


maybe ...

Function GetRidofAdjacentLineFeeds(ByVal s As String) As String
GetRidofAdjacentLineFeeds = Replace(s, vbNewLine & vbNewLine,
vbNewLine)
If InStr(s, vbNewLine & vbNewLine) = 0 Then
Exit Function
Else
GetRidofAdjacentLineFeeds = GetRidofAdjacentLineFeeds
(GetRidofAdjacentLineFeeds)
End If
End Function

assumption here is that you have taken nulls into account.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

Nov 12 '05 #6
MX1
Thanks Larry!

"Larry Linson" <bo*****@localhost.not> wrote in message
news:fv***************@nwrddc02.gnilink.net...
If this were in a Report, you might not need to concatenate the fields --
you could print each in a separate Text Box with its CanShrink property set to Yes.

But, because an empty field in an Access table would be Null, you could use:
= Name1 & (Chr$(13) + Chr$(10) + Name2) &
(Chr$(13) + Chr$(10) + Address1) &
(Chr$(13) + Chr$(10) + Address2) &
(Chr$(13) + Chr$(10) + CityStateZip) &
(Chr$(13) + Chr$(10) + Country)

The carriage return and line feed will "drop out" for any field that is
Null, because

anything + Null gives Null

whereas

something & Null is something.

I know that Lyle knows this, so I have to suppose that he was just having a little fun with you.

Larry Linson
Microsoft Access MVP

"MX1" <mx*@mx1.abc> wrote in message news:sQILb.10011$nt4.19309@attbi_s51...
Please help if you can. I have a form with an address field. There are 6 fields:

Name1
Name2
Address1
Address2
CityStateZip
Country

Ideally, I'd like the code to support never giving a blank line for an

empty
address field. I think I've got it except for Address2. I cannot get

it to
suppress the blank line even if Address2 is blank. Any advise would be
great. I've been going at this with trial and error for hours. Here's

the
code:

=[Name1] & Chr(13) & Chr(10) &
IIf([Name2]="","",[Name2] & Chr(13) & Chr(10)) &
IIf([Address1]="","",[Address1] & Chr(13) & Chr(10)) &
IIf([Address2]="","",[Address2] & Chr(13) & Chr(10)) &
IIf([CityStateZip]="","",[CityStateZip] & Chr(13) & Chr(10)) &
IIf([Country]="","",[Country])


Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Melissa | last post: by
7 posts views Thread by Richard Yardley | last post: by
1 post views Thread by DAnne | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.