Concatenating Text Strings

Concatenating Text Strings

You will often wish to concatenate (i.e. join) text strings, particularly if you store addresses as separate fields. Assuming that you have a table structure of:

AddressField1
AddressField2
AddressField3
AddressField4
AddressField5

and there is the possibility of certain fields being null. In a report you would therefore get:

The House

The Village
AnyTown
GU44 3ES

Which obviously won't look good on the report. There are two resolutions to this problem. You can either have just one text box, and concatenate the fields, or else you can use one text box per field, and use the CanGrow/CanShrink properties of the controls. Of the two methods, I prefer the first, as getting the conditions right for CanGrow/CanShrink is always tricky, and this method will also affect other controls on the report.

Add a new text box to the report, and set it's ControlSource to:

=([AddressField1]+Chr(13)+Chr(10)) & ([AddressField2]+Chr(13)+Chr(10)) & ([AddressField3]+Chr(13)+Chr(10)) & ([AddressField4]+Chr(13)+Chr(10)) & [AddressField5]

This will then output:

The House
The Village
Anytown
GU44 3ES

You will need to ensure that the textbox isn't named the same as one of the fields, as Access will get confused by the circular reference. This is one of many good reasons to use a naming convention, so you would have "txtAddressField1" being bound to "AddressField1"

Top

Last modified at 06/06/2006 15:02:36