databasedev.co.uk - database solutions and downloads for microsoft access

Concatenating Data

A handy tip on Concatenation, or Combining Microsoft Access data:

Use the plus sign '+' when creating a calculated field where you want Null returned if a field isn't filled in.

For example, if your table contains FirstName, LastName, and MiddleInitial fields and you would like to make the Full Name display in a query for a report.

Instead of using a long formula with an IIF statement checking the MiddleInitial field, you can use this instead:

FullName: [strFName] & (' '+[strMidInitial]) & ' ' & [strLName]

This formula says, if the middle initial is filled in then add it between the first and last name with a space in front of it. If the Middle Initial is blank, then a Null is returned, properly putting the first name, a space, then the last name.

The above example is for a calculated field in a query, for a calculated field on a report or form, replace the FullName: with an = sign, making:

=[strFName] & (' '+[strMidInitial]) & ' ' & [strLName]

This would create the control source for a text box on either a form or report.