By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,238 Members | 1,657 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,238 IT Pros & Developers. It's quick & easy.

Multiline address block in ms access 2013 using a query

P: 4
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
Post code
May 20 '15 #1
Share this Question
Share on Google+
6 Replies

Expert Mod 5K+
P: 5,397
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.
May 20 '15 #2

P: 4

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.

May 20 '15 #3

Seth Schrock
Expert 2.5K+
P: 2,951
Instead of concatenating the comma into the string, add Chr(13) & Chr(10)
May 20 '15 #4

P: 4
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.

May 20 '15 #5

Seth Schrock
Expert 2.5K+
P: 2,951
You have to have Chr(13) as well.
Expand|Select|Wrap|Line Numbers
  1. 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]
May 20 '15 #6

P: 4
Thanks Seth
Works well. very grateful.
May 20 '15 #7

Post your reply

Sign in to post your reply or Sign up for a free account.