concatenate, trim and nz

[allredkj]

I'm trying to concatenate an address with trim and nz. I need to determine whether or not ST_DIR1 and ST_SUFFIX exist. If they do not exist, then I want the ST_NAME1 and SUITE to display if any, else the entire address (concatenated with spaces in between and no leading or trailing spaces)

ConcAddr: [ST_DIR1] & Trim([ST_NAME1]) & " " & IIf(Nz([ST_SUFFIX],"")=""," " & [ST_SUFFIX] & " ") & [SUITE]

Example:
W Main Street #405
OR
Main Street

I appreciate your assistance.

 

[allredkj]

Sorry, here is the string I'm using in my query to create the new field for concatenated address:

ConcAddr: IIf(Nz([ST_DIR1],"", [ST_DIR1], " ")) & Trim([ST_NAME1]) & " " & IIf(Nz([ST_SUFFIX],"", [ST_SUFFIX], " ")) & Trim[SUITE]

 

[MSAccessRookie]

Sorry, here is the string I'm using in my query to create the new field for concatenated address:

ConcAddr: IIf(Nz([ST_DIR1],"", [ST_DIR1], " ")) & Trim([ST_NAME1]) & " " & IIf(Nz([ST_SUFFIX],"", [ST_SUFFIX], " ")) & Trim[SUITE]

If the above SQL Statement is the one that you are using, then your Syntax is incorrect. You also appear to have a Syntax Error in the Second Trim() Statement.

The proper Syntax for IIf() is:

IIf([Condition to test], [Value if TRUE], [Value if FALSE])
​

The proper Syntax for Nz() is:

Nz([String to test], [Value if NULL])
​

This would make your Query Something like the following:

ConcAddr: IIf(Nz([ST_DIR1],""[COLOR=red][B])[/B][/COLOR], [ST_DIR1], " "[COLOR=red][B])[/B][/COLOR] & Trim([ST_NAME1]) & " " & IIf(Nz([ST_SUFFIX],""[COLOR=red][B])[/B][/COLOR], [ST_SUFFIX], " "[B][COLOR=red])[/COLOR][/B] & Trim[COLOR=red][B]([/B][/COLOR][SUITE][COLOR=red][B])[/B][/COLOR]

Note that this assumes your intentions and is untested, so it may require adjustment on your part.

 

[vbaInet]

You can use Null propagation. Have a look at the link:

http://office.microsoft.com/en-us/a...-lines-in-labels-and-reports-HA001149472.aspx