Solved

Access Query concatenate address

Posted on 2012-03-28
8
353 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37776307
or this
 ([A1]+" ") & ([A2]+" ") & ([A3]+" ") & ([A4]+" ") & ([A5])
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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
 

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Changing a line of VBA using VBA 3 32
Reading the code behind a form - via VBA 3 30
Problem with vba code 4 36
Access Change color of current datasheet row. 1 22
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

791 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