Using Trim function in Query (AXP (2002) SP-1)

Topic

New Reply

I created a query the other day, using the trim function in the expression. It worked like a charm. As I try to run the query today, I get an error stating that the “function is not available in expressions in the query expression.” The expression is:

Address3: IIf([city]=” “,””,(Trim([City]) & “, ” & [STATE] & ” ” & Trim([ZIP])))

I have determined that it is the Trim function that is giving me fits. If I remove both trim functions, the expression works, but there are trailing spaces. This query ran fine last Friday when I created it. Anyone have any ideas???

Reply | Quote

Replies

This type of error message usually indicates that there is a problem with references.
Activate the Visual Basic Editor (Alt+F11).
Select Tools/References…
Uncheck references beginning with MISSING:
Click OK.
See if the query works now.

Reply | Quote

Hans– You da man!!

Your answer was right on! I don’t know what I’d do without you and the rest of the Loungers to turn to for support. Thanks to all.

Reply | Quote

I have run into this problem if the data has null values. I fixed it by concatenating a single blank to the end of the data field I am Trimming.

Reply | Quote