Getting data to start on a new line when writing a string. 1

[WallT]

If I am writing a string in a report...lets say an address string that is set to CanGrow...

=[CompanyName] & " " & [Address] & " " & [City] & ", " & [State] & " " & [Zip]

How can I tell the string to go to a new line after CompanyName, then after Address? Thank you in advance...

 

[BillPower]

This just an example using Carriage Return Line Feed:

= [CompanyName] & vbCrLf & _
[Address] & _
[City] & ", " & [State] & vbCrLf & _
[Zip]

 

[WallT]

I must be doing it wrong. I tried writing that directly in the ControlSource of a text box on a report, and it automatically put the vbCrLf in Parameters [ ]. I may not understand your answer. Do I need to write a function called vbCrLf? Thank you for your help.


=[CompanyName] & vbCrLf & [CustomersAddress] & vbCrLf & [CustomersCity] & ", " & [CustomersState] & " " & [CustomersZip]

 

[BillPower]

Sorry, didn't realise it was a Control Source, try this instead:

=[CompanyName]+Chr(13)+Chr(10) & [CustomersAddress] +Chr(13)+Chr(10) & [CustomersCity] & ", " & [CustomersState] & " " & [CustomersZip]

 

[WallT]

That worked great. Thank you.

 

[WallT]

Okay, I have run into a problem. I ran into situations where the entire address and name are going on the same line. What I have narrowed it down to is that when my first line is Null, then it strings everything on one line. Here is what I did in a text boxes row source on a report, and it works unless [LocationName] is Null. Is there a better way to handle this...

=[LocationName]+Chr(13)+Chr(10) & [ServiceAddress] & ", Suite " & [Suite/AptNo]+Chr(13)+Chr(10) & [ServiceCity] & ", " & [ServiceState] & " " & [ServiceZip]

 

[BillPower]

Try this in the On Open event of your Report:

If Not IsNull(Me![LocationName]) Then
Me!YourControlName.ControlSource = "=[LocationName]+Chr(13)+Chr(10) & [ServiceAddress] & "", Suite "" & [Suite/AptNo]+Chr(13)+Chr(10) & [ServiceCity] & "", "" & [ServiceState] & "" "" & [ServiceZip]"
Else
Me!YourControlName.ControlSource = "=[ServiceAddress] & "", Suite "" & [Suite/AptNo]+Chr(13)+Chr(10) & [ServiceCity] & "", "" & [ServiceState] & "" "" & [ServiceZip]"
End If

Delete YourControlName's ControlSource Property in the Report.

 

[WallT]

Thank you again. I didn't think of that.