Concatenate fields if not blank

[MrBuckingham]

Hi,

I have a table which holds separate fields for address data, eg:

SURNAME....HOUSENSTREET....AREA.....TOWN......POSTCODE
Smith.........23 High Street......Soho......London....W1D 4DJ

I need to concatenate this data, separated by commas. This I can do with expression builder:

ONEVAR: [SURNAME] & ", " & [HOUSENSTREET] & ", " & [AREA] & ", " & [TOWN] & " " & [POSTCODE]

Unfortunately, the 'AREA' field is sometimes empty - and in that case, my 'onevar' would include 2 consecutive commas. Is there any way I can change my expression to only include the AREA if it's something other than empty?

Many thanks
Darren

 

[MrBuckingham]

I found the answer here :
http://office.microsoft.com/en-us/a...alues-by-using-an-expression-HA010235851.aspx

I needed to use the + character for the field that may or may not be populated.

ONEVAR: [SURNAME] & ", " & [HOUSENSTREET] & (", "+[AREA]) & ", " & [TOWN] & " " & [POSTCODE]