Getting carriage returns in address field to print on labels

[TedT]

Hi,
I have a large database of contacts (7000+) that has 1 field for the address (was setup years ago this way and moved to Access later). In Access, each address line is separated with a carriage return so an address may have as many lines as needed - typically 2 lines - street, then city+state+zip, international will have a third line with the country, etc.


I am trying to print mailing labels, and all the addresses show up on 1 line, so the post office will not handle them.
I can't seem to figure out why the carriage returns are being ignored in the field and I hope there is someway to make this work, otherwise I will need to hand write all the labels.
Thanks
Ted T

[orange]

Please show some sample data, so readers can "test" their ideas.
If you have some code to process the addresses, you should post that as well.

[TedT]

I have no code, I used the label wizard. Not sure what data I can send, but it is pretty straight forward.

I have 1 field called address. In this field, the data is entered with carriage returns so on any form etc it looks like this:
mystreet
city, state, zip
country

So 3 separate lines.

The label wizard prints that field as:
mystreet city, state, zip country
all on one line.

I cannot go through 7000 records to split the address field into separate fields so I can arange the label - I only have the "address" field to print and it needs to be formatted as it appears everywhere else.

[June7]

I cannot replicate this issue. I built a report with the label wizard and the multi-line address shows as multiple lines. I used two methods to create the carriage returns and results are same:

1. ctrl + Enter

2. setting textbox EnterKeyBehavior for new line

This data should be in separate fields. If you really do have carriage return codes in the data, splitting into 3 parts should be quite easy. Then if the City/State/Zip really has commas, again should be easy to split. A VBA procedure could accomplish this - run it once and data is fixed and never have to do again as long as new data is entered correctly. Even a series of UPDATE queries could do this, without VBA.

[Xipooo]

Have you tried doing a replace?

replace(fieldname, chr(10) & chr(13), " ")

The worst you'd get is an extra space at the end of your address.

[June7]

Xippooo, correct order of codes for CR & LF is Chr(13) & Chr(10).

But how would replacing with a space force multi-line display?

[Xipooo]

Xippooo, correct order of codes for CR & LF is Chr(13) & Chr(10).

But how would replacing with a space force multi-line display?

It looks like I misread the OP. I thought it was a little odd that they would want it to be on one line.

[orange]

For what its worth I mocked up a couple of addresses. I entered the data directly into the table (tblMockAddresses) and used Ctrl Enter to enter CrLf's.

The data is shown in the graphic along with the 2-up labels I created using the Label wizard directly on the table.


To ensure the input data actually contained CrLF Chr(13) Chr(10), I used this routine

Sub testdec12()
    Dim x As Variant, i As Integer
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("tblMockAddresses")
    Do While Not rs.EOF
        x = Split(rs!ADDRLINE, Chr(13) & Chr(10))
        For i = LBound(x) To UBound(x)
            Debug.Print rs!id; i; x(i)
        Next i
        rs.MoveNext
    Loop
End Sub

and got this output

 1  0 27 Smith St.
 1  1 Kingston, Ontario
 1  2 CANADA
 2  0 2200 Lake Clark Drive
 2  1 Lake Worth
 2  2 Florida, USA,   33461

Since this data is meant to represent the OP's data, and since it works for me, I think there must be something the OP hasn't told us, or isn't aware of.

[ItsMe]

Hmmm, maybe using a textbox control's Text Format property to = Rich Text (located under the Data tab of the Property Sheet) would be beneficial.

[CJ_London]

I think there must be something the OP hasn't told us,

I think he has

each address line is separated with a carriage return

And as all responders are saying - it should be carriage return and line feed

[orange]

As suggested in post 2, show us some real data. We can only guess at what the OP is dealing with.

If the OP has a text line followed by chr(13) only, then he could replace chr(13) with chr(13) & chr(10).
We need to hear from the OP.

So simple to test --with sample data.
??? Did his data ever produce labels???