Carriage Return in calculated field

  • Thread starter Thread starter Dee
  • Start date Start date
D

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)
 
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.
 
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
((e-mail address removed)
Click to expand...
 
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
 
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 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: " & (e-mail address removed)
Click to expand...
 
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: " & (e-mail address removed)
Click to expand...
Click to expand...
 
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
 
Back
Top