Making a single field from a few

[coastguard]

Hi,

I've inherited a data base which has the address details of our members spread across multi fields i.e. Add 1, Add2, Add 3, Post town, Post code etc.

Not all the fields contain information which means when I do a mail merge for address labels there are blank lines.

I would like to either be able to create a single field in the table (like a memo field for example) which contains all the address detail, or create a mail merge without blank lines.

If anyone can help, or has other suggestions, I would greatly appreciate them.

Regards

 

[Pat Hartman]

There is no need to create a single field in the table. You can concatenate the fields in a query and use that for your mail merge.

Since some columns are null, you will want to use both the + and the & when concatenating them. The & is the standard concatenation character and is used the vast majority of the time because it ignores null values and so if you concatenate two columns, one of which is null, you still end up with the not null value. However, the plus sign when used to concatenate two values, one of which is null will return null and that is exactly the behavior you want in order to avoid extra spaces in the string. So try something like this:

Select (Addr1 + " ") & (Addr2 + " ") & (PostTown + " ") & (PostCode + " ") ... As FullAddr
From YourTable;

Notice the parentheses. Those are used to ensure that the + operation occurs between the two fields I want to nullify if one is null. If you used ampersands all the way, you would end up with extra spaces in place of the null values. I didn't test this so if the exact statement doesn't work, try pairing the fields with the space in front instead of behind.

 

[jdraw]

From a relational design view, you store data at the atomic level where you can. By that, it means break down the address into specific fields.

When you want to "assemble" these fields for some purpose eg mail merge and remove the "empty fields", you create some logic to do so.

How you store and how you present data are 2 different things.

 

[coastguard]

Thanks Pat and Jdraw.

Both replies very helpful and not only for this question.

REgards and hope the rest of your day?? goes well.