Solved

Access Query concatenate address

Posted on 2012-03-28
8
342 Views
Last Modified: 2012-06-27
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
Comment
Question by:HKFuey
  • 4
  • 2
  • 2
8 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37776247
IIF([A1] & ""<>"",[A1],"") & IIF([A2] & ""<>""," " & [A3],"")   & IIF([A3] & ""<>""," " & [A3],"")


if you want to make them appear in several lines add vbcrlf
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37776251
IIF([A1] & ""<>"",[A1],"") & IIF([A2] & ""<>""," " & [A2],"")   & IIF([A3] & ""<>""," " & [A3],"")
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37776307
or this
 ([A1]+" ") & ([A2]+" ") & ([A3]+" ") & ([A4]+" ") & ([A5])
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37776626
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:HKFuey
ID: 37777599
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
ID: 37777717
replace vbcrlf  with   chr(13) & chr(10)
0
 
LVL 9

Assisted Solution

by:macarrillo1
macarrillo1 earned 250 total points
ID: 37789134
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
 

Author Closing Comment

by:HKFuey
ID: 37800269
Got confused by this at first, then realised I needed a space between 'is' and 'null'
Thanks for the help
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now