• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

Access Query concatenate address

I have a db where addresses are stored:-
A1
A2
A3
A4
A5

Which can look like this: -

A1 = Street name
A2 = Town
A3 =
A4= County
A5= Post Code

Is there a way (in the query) I can concatenate the fields and remove the blanks?

So it look like this: -
Street name
Town
County
Post Code
0
HKFuey
Asked:
HKFuey
  • 4
  • 2
  • 2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
IIF([A1] & ""<>"",[A1],"") & IIF([A2] & ""<>""," " & [A3],"")   & IIF([A3] & ""<>""," " & [A3],"")


if you want to make them appear in several lines add vbcrlf
0
 
Rey Obrero (Capricorn1)Commented:
IIF([A1] & ""<>"",[A1],"") & IIF([A2] & ""<>""," " & [A2],"")   & IIF([A3] & ""<>""," " & [A3],"")
0
 
Rey Obrero (Capricorn1)Commented:
or this
 ([A1]+" ") & ([A2]+" ") & ([A3]+" ") & ([A4]+" ") & ([A5])
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
macarrillo1Commented:
Try:

iif(Trim([A1]) isnull, "", Trim([A1]) & vbcrlf ) &
iif(Trim([A2]) isnull, "", Trim([A2]) & vbcrlf ) &
iif(Trim([A3]) isnull, "", Trim([A3]) & vbcrlf ) &
iif(Trim([A4]) isnull, "", Trim([A4]) & vbcrlf ) &
iif(Trim([A5]) isnull, "", Trim([A5])  )
0
 
HKFueyAuthor Commented:
Hi guys, thanks for the quick response vbcrlf does not seem to be recognised in query builder, does this mean I have to create a function?
0
 
Rey Obrero (Capricorn1)Commented:
replace vbcrlf  with   chr(13) & chr(10)
0
 
macarrillo1Commented:
Using what @capricorn1 suggested Try:

iif(Trim([A1]) isnull, "", Trim([A1]) & chr(13) & chr(10)) &
iif(Trim([A2]) isnull, "", Trim([A2]) & chr(13) & chr(10)) &
iif(Trim([A3]) isnull, "", Trim([A3]) & chr(13) & chr(10)) &
iif(Trim([A4]) isnull, "", Trim([A4]) & chr(13) & chr(10)) &
iif(Trim([A5]) isnull, "", Trim([A5])  )
0
 
HKFueyAuthor Commented:
Got confused by this at first, then realised I needed a space between 'is' and 'null'
Thanks for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now