When you combine text values from several fields, some records might contain empty fields, resulting in output that might not be what you want. For example, the output might include extra spaces.
To compensate for records that don't have values in certain fields, you can use the IIf function. You use the IIf function when you want the value (or absence of a value) in one field to determine which one of two possible results you want returned.
For example, if you know that not every record will have a value in the Region field, use the following expression:
=IIf(IsNull([Region]),""," "&[Region])
If the Region field is empty, Microsoft Access returns an empty string, as specified by the argument "". If the Region field contains a value, Microsoft Access returns a space and the region, as specified by the argument " " & [Region].
Note The Invoice report in the Northwind sample database has an example of combining values from text fields by using the IIf function. To view this report, open the Northwind database in the Samples subfolder of your Microsoft Office folder, and then open the Invoice report in Design view.
Manipulating text values
You can manipulate a text string by using a function, such as Left or Trim. For example, set the ControlSource property of a text box to the following expression to display the value of the Address field without any leading or trailing spaces.
=Trim([Address])