Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:

    <font color=blue>Address3: IIf([city]=" ","",(Trim([City]) & ", " & [STATE] & " " & Trim([ZIP])))</font color=blue>

    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???

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •