Access Queries: Concatenate Multiple Fields Into A New Field Seperated By New Lines (vbCRLF)

Often I come across a database which has been designed with multiple address line fields.

We usually want to concatenate these multiple address lines into a new field with each of the old fields on a new line.

To do this I usually create a new query and use something like the following SQL:

SELECT IIf(Not IsNull([Addr1]),[Addr1] & Chr(13) & Chr(10),"")
& IIf(Not IsNull([Addr2]),[Addr2] & Chr(13) & Chr(10),"")
& IIf(Not IsNull([Addr3]),[Addr3] & Chr(13) & Chr(10),"")
& IIf(Not IsNull([Addr4]),[Addr4] & Chr(13) & Chr(10),"")
AS AddressPart, tblPerson.PersonID INTO newaddress
FROM tblPerson;

Where addr1, addr2, addr3 and addr4 are the old single line address fields, AddressPart is the new field with each of the single lines combined and newaddress is the temporary table I want to put the results of the query into.

This checks if each of the original fields are NULL and, if not, adds them to the new field followed by Chr(13) and Chr(10) (the equivalent of a vbCrLf). If the field is NULL it is ignored.

I usually put the results of the query into a new table and then use an UPDATE statement to combine this with the existing table.

Although this approach isn’t very elegant it allows me to retain each of the seperate fields until I’ve imported the new joined up address and checked if everything has worked okay.

Advertisements

0 Responses to “Access Queries: Concatenate Multiple Fields Into A New Field Seperated By New Lines (vbCRLF)”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Blog Stats

  • 221,277 hits

Counter

ClustrMap


%d bloggers like this: