New Line In Field

[Learn2010]

I have an Access 2010 web database. I have a report that I am trying to get a multiline result in a text box field. I am using:

=[SchoolAddress1] & Chr(10) & [SchoolAddress2] & Chr(10) & [SchoolCity] & ", " & [SchoolState] & " " & [SchoolZipCode]

I want to get:

123 Main Street
Apt. 123
Anytown, WA 12345

Instead I get:

123 Main StreetApt. 123Anytown, WA 12345

I get a single line. Any help on getting three lines?

 

[DCrake]

Try either vbNewline or vbCrLf

 

[Learn2010]

It didn't work.

 

[DCrake]

Well it works in all other versions of Access and VB

 

[Banana]

I don't think vbNewline & vbCrLf are valid in a Macro context (remember, they're Visual Basic constants and thus not available in a web context) - you would probably need to do it this way:

Chr(13) & Chr(10) which should be basically same as vbCrLf.

 

[DCrake]

Can't you tell I know naff all about web programming? If only, if only...

 

[Banana]

If only they would at least carry over the constants? Yeah, that'd be nice. I'd rather type vbNewLine than try and remember it's Chr(13) & Chr(10) (and hope to hell I got the order right.... In fact, I had to look it up to answer the question)

 

[Learn2010]

I have tried variations of this:

=[SchoolAddress1] & Chr(13) & Chr(10) & [SchoolAddress2] & Chr(13) & Chr(10) & [SchoolCity] &", "&[SchoolState] & [SchoolZipCode]

to get this:

SchoolAddress1
SchioolAddress2
SchoolCity, SchoolState SchoolZipCode

but to no avail.

 

[Banana]

Okay, that's odd then.

What is actual output?

 

[Learn2010]

I only get SchoolAddress1. Let me back up. This is a control on a web database report.

 

[Banana]

Could this be a case of the textbox not displaying the whole content due to lack of height? Even if you have enough height allocated, what happpens when you put a cursor in that textbox, then press down?

Edit: Doh, saw it's a report, not a form. Try and do this in a form. What happens then?

 

[Learn2010]

I get the same thing; SchoolAddress1.

 

[ghudson]

Any reason you are not using all three fields (one on top of the other) in the report?

 

[Learn2010]

Yes, there is. However, I can live with that and adjust to it. Thanks.

 

[DCrake]

A silly question but is there actual data in the second and subsequent fields?

Have you tried Debug.Print SchoolAddress after you code. And step through the code using a break point.

 

[Learn2010]

Yes there is data. There is no code on the web report. I just put this into a field and it doesn't work.

 

[DCrake]

Try and open this accdb in A2010 and open the query.

Does it work?

 

[Learn2010]

The query works.

 

[DCrake]

So you need to replicate what I have done with your table and fields.

Notice the Replace() function. This removes blank lines from the address block if some address fields are blank.


You could improve this by using a function to do it

Public Function FormattedAddress(Adr1 As String, Adr2 As String, Adr3 As String, Adr4 As String, PCode As String) As String


Dim StrAddress As String
Dim NewLine 

NewLine = Chr(13) & Chr(10)

'/Concat all lines together into one string
StrAddress = Adr1 & Newline & Adr2 & Newline & Adr3 & NewLine & Adr4 & NewLine & PCode

'/Attempt to remove double carriage returns

StrAddress = Replace(StrAddress,NewLine & NewLine,NewLine)

FormattedAddress = StrAddress

End Function

Then in you query which will become the underlying recordsource of your report

Address:FormattedAddress([Addr1],[Addr2],[Addr3],[Addr4],[Postcode])

 

[Banana]

David, I hate to remind you but if this is a web report.... No VBA.

But I think David is actually on the right track - if you use a query instead of trying to format the controls, you may be able to get what you want.