TLC Tips of the Week:                          from Helen Olberg

This week’s tip applies to Microsoft Excel and Access.

When making up lists of names and addresses, you’ve always wondered which is better, to put the first name, middle name or initial, and last name in separate columns, or type them in as you want them to show. Sometimes you want them to show as Smith, Mary L. and sometimes as Mary L. Smith. You also want to indicate Dear Mary or Dear Mary L. varied for formal and informal occasions.

For flexibility you’ll be better off setting up each component as a separate field, then combining them into the pattern that you want with a formula in either Excel or an Access query / form / report.

Excel:

Assuming that the columns have been set up as above, in a separate cell (such as column F5) type the formula shown to get the result on the right. Be careful to include spacebars between quotation marks and all punctuation as shown. Copy the formula down to the lower rows.

Access:

Assuming that the fields have been set up with column names as above, in a query you should create a new field as below. Give the column a name by typing your desired name followed by the colon (:) in front of the formula. In my example I use Addressee. Always include spacebars before and after the ampersand (&) character and where indicated within quotation marks. When you run the query or use the query as the basis of a form or report, you’ll see the formula results.

Back to the TLC Tips page