Carriage Return in calculated field

Dee · Jul 24, 2003

I have input a formula to put in a carriage return in a
field; however, when there is no information, it produces
blank lines. I would like for it all to be together. I
tried to send a message through outlook express earlier,
but it didn't seem to appear, so I hope it doesn't somehow
end up here twice. First, I'll give you the formula I
used, then an example how it comes out:

Formula:

=IIf(IsNull([firstname]),"","" & [firstname]) & IIf(IsNull
([Last Name]),""," " & [Last Name]) & Chr(13) & Chr(10) &
[CHCompany] & IIf(IsNull([Division]),""," " & [Division])
& Chr(13) & Chr(10) & IIf(IsNull([Address]),"","" & Chr
(13) & Chr(10) & [Address]) & IIf(IsNull([Address2]),"",""
& Chr(13) & Chr(10) & [Address2]) & Chr(13) & Chr(10) & IIf
(IsNull([City]),"","" & [City]) & IIf(IsNull
([State]),"",", " & [State]) & IIf(IsNull([Zip]),""," " &
[Zip]) & IIf(IsNull([Country]),""," " & Chr(13) & Chr(10)
& [Country]) & Chr(13) & Chr(10) & IIf(IsNull
([Phone]),"","Phone: " & [phone]) & Chr(13) & Chr(10) & IIf
(IsNull([Fax]),"","Fax: " & [fax]) & Chr(13) & Chr(10) &
IIf(IsNull([mobile]),"","Mobile: " & [mobile]) & Chr(13)
& Chr(10) & IIf(IsNull([pager]),"","Pager: " & [pager]) &
Chr(13) & Chr(10) & IIf(IsNull([homephone]),"","Home
Phone: " & [homephone]) & Chr(13) & Chr(10) & IIf(IsNull
(),"","Email: " & [email])

Here's how it comes out (Example):

FirstName LastName
CHCompany Division

Address2
City, State Zip
Country
Phone: 111.111.1111
Fax: 111.111.1111

Email: [email protected]

The fields Address, Mobile, Pager, and Home Phone had no
data and it inputed blank lines (I suppose due to where I
put & Chr(13) & Chr(10)formula). Can anyone tell me how
to avoid this?? Hopefully tonight

. And is it
different in reports and forms, because I have this same
formula in a form and need to make sure it doesn't do the
same thing

.

Thanks!

Dee

Duane Hookom · Jul 24, 2003

Consider a simple expression like
Address1 + Chr(13) +Chr(10) & Address2
vs
Address1 & Chr(13) & Chr(10) & Address2
In the first expression, if Address1 is null, then Chr(13) + Chr(10) are
drug into Nulldom due to the "+". So the first expression would not have a
carriage return while the second expression will always have a carriage
return regardless if Address1 is null.

You should be able to apply this knowledge to all of your carriage returns
and IsNull()s.

HSalim · Jul 24, 2003

Ouch, that is some ugly code (just kidding)

I use a function in my application that does something similar. This
example will get you started.
Once you complete it, you can use it everywhere - in queries, reports etc

Public Function ConcatAddress(Fname as string, LName as String, Address1 as
String, Address2 as String, CITY As String, STATE As String, Zip As String)
As String

Dim strAddress as string

strAddress = ""
If Not IsNull(Fname) And Trim(Fname) <> "" then strAddress = StrAddress
& Fname & ", "
if not IsNull ([Last Name]) And trim(LName) <> "" then strAddress =
StrAddress & Lname
If StrAddress <> "" then StrAddress = strAddress & vbcrlf

If not IsNull(Address1) And Trim(Address1) <> "" then strAddress =
StrAddress & trim(Address1) & vbcrlf
If not IsNull(Address2) And Trim(Address2) <> "" then strAddress =
StrAddress & trim(Address2) & vbcrlf
StrAddress = StrAddress & Trim(CITY) & ", "
StrAddress = StrAddress & Trim(STATE) & " "
StrAddress = StrAddress & Trim(Zip)

' Add similar lines for the rest of the stuff

ConcatAddress = StrAddress

End Function

Dee said:
I have input a formula to put in a carriage return in a
field; however, when there is no information, it produces
blank lines. I would like for it all to be together. I
tried to send a message through outlook express earlier,
but it didn't seem to appear, so I hope it doesn't somehow
end up here twice. First, I'll give you the formula I
used, then an example how it comes out:

Formula:

=IIf(IsNull([firstname]),"","" & [firstname]) & IIf(IsNull
([Last Name]),""," " & [Last Name]) & Chr(13) & Chr(10) &
[CHCompany] & IIf(IsNull([Division]),""," " & [Division])
& Chr(13) & Chr(10) & IIf(IsNull([Address]),"","" & Chr
(13) & Chr(10) & [Address]) & IIf(IsNull([Address2]),"",""
& Chr(13) & Chr(10) & [Address2]) & Chr(13) & Chr(10) & IIf
(IsNull([City]),"","" & [City]) & IIf(IsNull
([State]),"",", " & [State]) & IIf(IsNull([Zip]),""," " &
[Zip]) & IIf(IsNull([Country]),""," " & Chr(13) & Chr(10)
& [Country]) & Chr(13) & Chr(10) & IIf(IsNull
([Phone]),"","Phone: " & [phone]) & Chr(13) & Chr(10) & IIf
(IsNull([Fax]),"","Fax: " & [fax]) & Chr(13) & Chr(10) &
IIf(IsNull([mobile]),"","Mobile: " & [mobile]) & Chr(13)
& Chr(10) & IIf(IsNull([pager]),"","Pager: " & [pager]) &
Chr(13) & Chr(10) & IIf(IsNull([homephone]),"","Home
Phone: " & [homephone]) & Chr(13) & Chr(10) & IIf(IsNull
(),"","Email: " & [email])

Here's how it comes out (Example):

FirstName LastName
CHCompany Division

Address2
City, State Zip
Country
Phone: 111.111.1111
Fax: 111.111.1111

Email: [email protected]

The fields Address, Mobile, Pager, and Home Phone had no
data and it inputed blank lines (I suppose due to where I
put & Chr(13) & Chr(10)formula). Can anyone tell me how
to avoid this?? Hopefully tonight . And is it
different in reports and forms, because I have this same
formula in a form and need to make sure it doesn't do the
same thing .

Thanks!

Dee[/QUOTE]

Dee · Jul 24, 2003

Thanks Duane and HSalim for your help! It is very much appreciated. And I
see that other message did eventually show up.... patience is a virtue!

Have a great day!

Dee

Dee · Jul 25, 2003

. And is it
different in reports and forms, because I have this same
formula in a form and need to make sure it doesn't do the
same thing

.

Thanks!

Dee[/QUOTE]

Marshall Barton · Jul 25, 2003

Dee said:
Forget the message right before this... I messed up on the "it needs to look
like this part":

Well, it doesn't work the way I need it to. I'll show you what happens with
each formula:

When I try the simple formula, which is below,

=[firstname] & " " & [Last Name]+Chr(13)+Chr(10) & [CHCompany] & " " &
[Division]+Chr(13)+Chr(10) & [Address]+Chr(13)+Chr(10) &
[Address2]+Chr(13)+Chr(10) & [City] & ", " & [State] & " " &
[Zip]+Chr(13)+Chr(10) & [Country]+Chr(13)+Chr(10) & "Phone: " &
[phone]+Chr(13)+Chr(10) & "Fax: " & [fax]+Chr(13)+Chr(10) & "Mobile: " &
[mobile]+Chr(13)+Chr(10) & "Pager: " & [pager]+Chr(13)+Chr(10) & "Home
Phone: " & [homephone]+Chr(13)+Chr(10) & "Email: " &

This happens:

Suzan Erdal
3 E Industrial Equipment & Instrumentation Ltd. Ataturk Sitesi Hilm
Cayiroglu Sok
Mavi Cam Apt. No: 3-5 OR-AN
Ankara, 06540
Turkey
Phone: 90.312.491.3450
Fax: 90.312.491.3459
Mobile: Pager: Home Phone: Email: [email protected]

It needs to look like this:

Suzan Erdal
3 E Industrial Equipment & Instrumentation Ltd.
Ataturk Sitesi Hilm Cayiroglu Sok
Mavi Cam Apt. No: 3-5 OR-AN
Ankara, 06540
Turkey
Phone: 90.312.491.3450
Fax: 90.312.491.3459
Email: [email protected][/QUOTE]

Dee, you need to use some parenthesis to associate each item
with the parts it's supposed to suppress when it's null:

=( [firstname] + " " ) & [Last Name] & Chr(13)+Chr(10) &
[CHCompany] & ( " " + [Division] ) & Chr(13)+Chr(10) &
( [Address] + Chr(13)+Chr(10) ) &
( [Address2] + Chr(13)+Chr(10) ) &
[City] & ( ", " + [State] ) & " " [Zip] & Chr(13)+Chr(10) &
( [Country] + Chr(13)+Chr(10) ) &
( "Phone: " + [phone] + Chr(13)+Chr(10) ) &
( "Fax: " + [fax] + Chr(13)+Chr(10) ) &
( "Mobile: " + [mobile] + Chr(13)+Chr(10) ) &
( "Pager: " + [pager] + Chr(13)+Chr(10) ) &
( "Home Phone: " + [homephone] + Chr(13)+Chr(10) ) &
( "Email: " + [email] )

Dee · Jul 25, 2003

Thanks Marsh! I'll try that one... I'm on a definite learning curve here

!!

Have a great day!

Dee

Marshall Barton said:
Dee said:

Forget the message right before this... I messed up on the "it needs to look
like this part":

Well, it doesn't work the way I need it to. I'll show you what happens with
each formula:

When I try the simple formula, which is below,

=[firstname] & " " & [Last Name]+Chr(13)+Chr(10) & [CHCompany] & " " &
[Division]+Chr(13)+Chr(10) & [Address]+Chr(13)+Chr(10) &
[Address2]+Chr(13)+Chr(10) & [City] & ", " & [State] & " " &
[Zip]+Chr(13)+Chr(10) & [Country]+Chr(13)+Chr(10) & "Phone: " &
[phone]+Chr(13)+Chr(10) & "Fax: " & [fax]+Chr(13)+Chr(10) & "Mobile: " &
[mobile]+Chr(13)+Chr(10) & "Pager: " & [pager]+Chr(13)+Chr(10) & "Home
Phone: " & [homephone]+Chr(13)+Chr(10) & "Email: " &

This happens:

Suzan Erdal
3 E Industrial Equipment & Instrumentation Ltd. Ataturk Sitesi Hilm
Cayiroglu Sok
Mavi Cam Apt. No: 3-5 OR-AN
Ankara, 06540
Turkey
Phone: 90.312.491.3450
Fax: 90.312.491.3459
Mobile: Pager: Home Phone: Email: [email protected]

It needs to look like this:

Suzan Erdal
3 E Industrial Equipment & Instrumentation Ltd.
Ataturk Sitesi Hilm Cayiroglu Sok
Mavi Cam Apt. No: 3-5 OR-AN
Ankara, 06540
Turkey
Phone: 90.312.491.3450
Fax: 90.312.491.3459
Email: [email protected][/QUOTE]

Dee, you need to use some parenthesis to associate each item
with the parts it's supposed to suppress when it's null:

=( [firstname] + " " ) & [Last Name] & Chr(13)+Chr(10) &
[CHCompany] & ( " " + [Division] ) & Chr(13)+Chr(10) &
( [Address] + Chr(13)+Chr(10) ) &
( [Address2] + Chr(13)+Chr(10) ) &
[City] & ( ", " + [State] ) & " " [Zip] & Chr(13)+Chr(10) &
( [Country] + Chr(13)+Chr(10) ) &
( "Phone: " + [phone] + Chr(13)+Chr(10) ) &
( "Fax: " + [fax] + Chr(13)+Chr(10) ) &
( "Mobile: " + [mobile] + Chr(13)+Chr(10) ) &
( "Pager: " + [pager] + Chr(13)+Chr(10) ) &
( "Home Phone: " + [homephone] + Chr(13)+Chr(10) ) &
( "Email: " + [email] )
[/QUOTE]

Click to expand...

Dee · Jul 26, 2003

Marsh,

Thought I'd let you know that your suggestion worked like a dream!

Thanks a million!!! You all don't know what it means (actually you probably
do), that you all have helped me with these questions no matter how silly
they may be.

Have a great weekend!

Dee

Carriage Return in calculated field

Dee

Duane Hookom

HSalim

Dee

Dee

Marshall Barton

Dee

Dee