MS Access concatenate and trim 1

[sweetkel23]

I'm trying to concatenate an address with trim. 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

I appreciate your assistance.

 

[sweetkel23]

I'm sorry, here is the string I used in my query to create the new concatenated address field:

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

 

[MajP]

Try this.

[ST-DIR1] + " " & Trim([ST_NAME1]) & " " + [ST_Suffix]

The reason is that the "+" sign acts different with a null.
null + string = null
null & string = string

So if there are values it acts like a regular concatenation, but if they are null

null + " " & Trim([ST_NAME1]) & " " + Null
=
null & Trim([ST_NAME1]) & null
=
Trim([ST_NAME1])

 

[sweetkel23]

Thank you MAJP, you've just saved me! You're a gem! Thank you soooo much!!!!!

 

[sweetkel23]

I added the SUITE as shown below but getting "undefined function 'trim' in expession:

ConcatAdd: [ST_DIR1]+" " & Trim([ST_NAME1]) & " "+[ST_Suffix] & " "+[SUITE]

 

[MajP]

Not sure, but sometimes you see problems with VBA string functions if the reference to vba needs to be refreshed. Test the trim function in the immediate window and see if you get an error. Also try other string functions such as right, left, len, instr, mid, etc. If so unclick the reference to vba and reclick. If you cannot unclick it create a new database and import all objects into the fresh database.

 

[PHV]

Does the DB compiles properly ?
No missing references ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sweetkel23]

You were right, the query worked in a new database. Thanks for helping me realize my query was corrupted, probably since I made a lot of changes. Thank you so much!