• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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