Proper Formatting of Postal Addresses

[SmallTime]

Hi all,

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?

Many thanks

[debasisdas]

Can you post a sample postal addresses (just to understand your question better).

[SmallTime]

Prefix DoorNo Suffix Address1 Address2 Address3 City County PostCodeFlat 1 10 Bidden House Aanwell Street Luton Hertfordshire TT7 1EF
East Side 64 Farnham Grove Doncaster ST5 9TU
66 Everest Steeple Green Hertfordshire

The formatting's not too good but if you look at the headings you'll ber the gist of it. I've also attached a table in a word doc.

[AndreRet]

Although this works fine there is a limit to the amount of code that the text box control source can hold

Not sure what you mean by this, You can add as much code as you want into any given sub for a text box.:)

As far as the amount of text the text box can hold, use the same code but with a rich text format box (MS Word is using rich text boxes as a document). You are basically not limited to the amount of text it can take.