Concatenate and Trim (1 Viewer)

[allredkj]

I have a table, containing the following data points:
ACCT_NUM
ST_NUM1
ST_DIR
ST_NAME1
ST_SUFFIX
SUITE
SITE_ADDR


Using an update query, I want to concatenate and trim:
ST_NUM1
ST_DIR
ST_NAME1
ST_SUFFIX
SUITE


and place into the SITE_ADDR field, so that the data in SITE_ADDR looks like (for different scenarios):
N Main Street #107
Main Street #107
Main Street
Smith Rd #10
Smith #10
W Central Ave #505
(no spaces where ST_DIR is null AND no spaces where ST_SUFFIX is null if SUITE exists)



Is there a way to use this query for the above? I keep getting the following error: "undefined function 'Trim' in expression".

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

I get the same error for this longer version of the query:
SITE_ADDR: IIf(Nz([ST_DIR1],""), [ST_DIR1], " ") & Trim([ST_NAME1]) & " " & IIf(Nz([ST_SUFFIX],""), [ST_SUFFIX], " ") & Trim([SUITE])

If I could get either one of them to work, that would be great. Thanks in advance for your assistance.

Kelly

 

[Alansidman]

The trim function removes any spaces at the beginning and/or end of the field value. Do your records for the field ST_NAME1 or the field SUITE contain unwanted spaces? Perhaps, it might be quicker to clean up your table by running the Trim function against those fields and then running an update query to replace those records. Then you will not need to run the trim as part of your expression.

I am not sure why it does not work in your expression, however, as that looks normal.

Just another way to get to the end results.

 

[allredkj]

Thanks Alan.... so can I do an update query to accomplish this?

So then in the 'update to', do I insert the following:
Trim([ST_DIR1]) one for every data point?

 

[Alansidman]

This video will show you how to run update queries to your table.

http://www.datapigtechnologies.com/flashfiles/datamanipulate.html

Alan