Multiline address block in ms access 2013 using a query

[Shute112]

I have a table with address field and want to have a multi line address block on a form and report. What is the code in a string for line return. Example

First name surname
Address 1
Addess2
Town
County
Post code

[zmbd]

Shute112:
How is your data stored?

In a single field like:
[First name surname Address 1 Addess2 Town County Post code]

or in a table like:

[First name][surname][Address 1][Addess2][Town][County][Post code]

In the first case you will need to code a function to split out your data. I also highly advise you to redesign your database in this case.

In the second case, place the fields on your report in the desired positions.

[Shute112]

Hi ZMBD

Thanks for coming back to me so quickly. The table consists of separate fields for the name address etc. I have jointed the fields together into one string.

Addr: (+[HouseName]+",") & " " & (+[Address]+", ") & (+[Area]+", ") & (+[Town] & ", ") & [County] & ", " & [Postcode]

but would like to see the address in separate lines like an envelope is addressed. Is there a code to go between the fields to have a line break.

Currently the address appears in one long line and not multiable lines as I indicated in my question. The problem with placing the fields separately onto the report if the customer has not a house name or only one line of address there is a blank space.

Hope this makes sense.

Mike

[Seth Schrock]

Instead of concatenating the comma into the string, add Chr(13) & Chr(10)

[Shute112]

Hi Seth

I have tried your Chr(10) but cannot get it to work. Can you please look at the string and see where I should be adding the Chr(10) and if possible could you type it out and I will copy straight into my query.
Addr: (+[HouseName]+",") & " " & (+[Address]+", ") & (+[Area]+", ") & (+[Town] & ", ") & [County] & ", " & [Postcode]

I added the chr(10) as below. Was I right!!!

Addr: (+[HouseName]+Chr(10)) & " " & (+[Address]+"Chr(10) ") & (+[Area]+"Chr(10) ") & (+[Town] & "Chr(10) ") & [County] & "Chr(10) " & [Postcode]

But this did not work.


Mike

[Seth Schrock]

You have to have Chr(13) as well.

Addr: ([HouseName]+Chr(13)+Chr(10)) & ([Address]+Chr(13)+Chr(10)) & ([Area]+Chr(13)+Chr(10)) & ([Town] +Chr(13)+Chr(10)) & ([County]+Chr(13)+Chr(10)) & [Postcode]

[Shute112]

Thanks Seth
Works well. very grateful.
Mike