Carriage Return in calculated field

[Dee]

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
((e-mail address removed)

 

[Duane Hookom]

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]

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

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
((e-mail address removed)

 

[Dee]

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]

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: " & (e-mail address removed)

 

[Marshall Barton]

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: " & (e-mail address removed)

 

[Dee]

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

Have a great day!

Dee

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: " & (e-mail address removed)

 

[Dee]

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