Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access Query concatenate address

Posted on 2012-03-28
8
Medium Priority
?
372 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 1000 total points
ID: 37777717
replace vbcrlf  with   chr(13) & chr(10)
0
 
LVL 9

Assisted Solution

by:macarrillo1
macarrillo1 earned 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

877 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