I'm trying to format postal addresses in a text field and want to avoid placing extra spaces between those fields that that are null or have zero length strings.
Here's a shortened version of what I tried:
IIf(IsNull([nmsPrefix]) Or [nmsPrefix]="","",[nmsPrefix] & " ") & IIf(IsNull([nmsNo]) Or [nmsNo]="","",[nmsNo] & " ")
Although this works fine there is a limit to the amount of code that the text box control source can hold, and if I format the whole address as the above it'll contain too many characters.
Question is; is there a more elegant and shorter way for formatting an address, bearing in mind null and zero strings, so that spaces only appear after valid entries?