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.