locked
Trim function not working... RRS feed

  • Question

  • why..

    CustName: RTrim(Left(Mid([FormBody],InStr([FormBody],"Customer First Name •")+22),InStr(Mid([FormBody],InStr([FormBody],"Customer First Name •")-0),"Customer Last Name • ")-23)

    If there are trailing spaces after the name, all spaces show in Query. 

    I am using Access 2010 (if that is an issue)

    Wednesday, February 2, 2011 2:35 AM

Answers

  • Hello,

    Most likely, you see carriage return symbols Chr(10) and Chr(13) as spaces. Clean up your string first:

    FormBody=Replace(FormBody,vbCRLF," ")

     or:

    FormBody=Replace(FormBody,vbCR," ")

    FormBody=Replace(FormBody,vbLF," ")

    Nadia

    • Marked as answer by Mark Matzke Wednesday, February 2, 2011 2:31 PM
    Wednesday, February 2, 2011 4:47 AM

All replies

  • Mark,

    open the VBA editor (design view on a module - create one if you must).

    The go to the Tools->References doalog and look at all the referenced that are checked off (they will be at the top of the list). If any of those are marked as "Missing:", then you have a broken reference, and this can easily cause the errors that you are seeing.

    uncheck any missing references and try your query a again.

    if this does not resolve the issue - shout back.


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    Wednesday, February 2, 2011 2:39 AM
  • "Mark Matzke" wrote in message
    news:1acae3ca-6ae6-498a-9936-ecfe56594514@communitybridge.codeplex.com...
    > why..
    >
    > CustName: RTrim(Left(Mid([FormBody],InStr([FormBody],"Customer First
    > Name •")+22),InStr(Mid([FormBody],InStr([FormBody],"Customer First
    > Name •")-0),"Customer Last Name • ")-23)
    >
    > If there are trailing spaces after the name, all spaces show in Query.
    >
    > I am using Access 2010 (if that is an issue)
    >
     Seeing that your data contains the unusual character "•", I suggest you
    verify that the characters you think are spaces are really standard
    spaces -- Chr(32) -- and not some other non-printing character.
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Wednesday, February 2, 2011 4:16 AM
  • Hello,

    Most likely, you see carriage return symbols Chr(10) and Chr(13) as spaces. Clean up your string first:

    FormBody=Replace(FormBody,vbCRLF," ")

     or:

    FormBody=Replace(FormBody,vbCR," ")

    FormBody=Replace(FormBody,vbLF," ")

    Nadia

    • Marked as answer by Mark Matzke Wednesday, February 2, 2011 2:31 PM
    Wednesday, February 2, 2011 4:47 AM
  • I'm having a similar problem. Our office was upgraded to 2010 a couple of weeks ago and since then Access VBA apps that have run properly for years won't compile. The first error I get is that the reference for the "Trim" function isn't available. There are no "missing' flags in the tools/reference list and I've tried adding anything that made sense at all. There doesn't appear to be a place where one can look up the reference containing a particular function. Anyone had any luck fixing this?
    Monday, February 7, 2011 7:53 PM
  • The Trim function is in the VBA library. That's not likely the issue, though (although you can try seeing whether using VBA.Trim instead of just Trim makes a difference).

    Go back to the References list. Select one of the unchecked references at random. Back out of the dialog, then go back in and uncheck the one you just selected. Any difference? If not, write down all of the references that are checked, and try to unselect them all. (Access won't actually let you, but unselect as many as you can). Back out of the dialog, then go  back in and reselect all of the ones that you succeeded in unselecting.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Monday, February 7, 2011 8:39 PM
  • "jdhouse" wrote in message
    news:7cc90461-b9b4-4758-b0df-323d376e3b8e@communitybridge.codeplex.com...
    > I'm having a similar problem. Our office was upgraded to 2010 a couple of
    > weeks ago and since then Access VBA apps that have run properly for years
    > won't compile. The first error I get is that the reference for the "Trim"
    > function isn't available. There are no "missing' flags in the
    > tools/reference list and I've tried adding anything that made sense at
    > all. There doesn't appear to be a place where one can look up the
    > reference containing a particular function. Anyone had any luck fixing
    > this?
     Does any VBA code run in the database at all?  Maybe it's just that you need
    to put the databases in a trusted location, or designate their folders as
    trusted locations.
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Proposed as answer by wikuid Friday, February 14, 2014 5:00 PM
    • Unproposed as answer by wikuid Friday, February 14, 2014 5:00 PM
    Tuesday, February 8, 2011 3:18 AM
  • I just had same problem in Excel. VBA Trim failed even after checking that every "space" truly was chr(32).

    WorksheetFunction.Trim worked, however, so suggest this approach in Excel to deal with what appears to be an occasional (?) bug in VBA.Trim

    This is my first post so excuse me (and let me know please) if I put it in the wrong place.
    • Edited by wikuid Friday, February 14, 2014 5:07 PM
    Friday, February 14, 2014 5:05 PM
  • I just had same problem in Excel. VBA Trim failed even after checking that every "space" truly was chr(32).

    WorksheetFunction.Trim worked, however, so suggest this approach in Excel to deal with what appears to be an occasional (?) bug in VBA.Trim

    If VBA is working at all, I've never seen VBA.Trim fail to behave as designed -- it has always been the data that was not as "trimmable" as people thought.  I would definitely want to see the data in question, to verify your report.  If there reallty is a bug, we could report it to Microsoft.  This was in Excel, you say?  Could you by any chance send me the workbook that contained the failing code, or a cut-down sample that demonstrates the bug?

    This is my first post so excuse me (and let me know please) if I put it in the wrong place.

    Well, this particular forum is for Microsoft Access, rather than Excel, so maybe it's the wrong place.  But if there really is a problem with the VBA Trim function, we'd all like to kno0w about it.



    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, February 14, 2014 6:00 PM
  • Your answer was "spot on", thank you.   I am working in MS Access 2016 and the "TRIM()" function failed when I opened an older version of MS Access.  I opened the reference list in VBA and selected the "Missing ...." item.  It worked.

    Friday, January 27, 2017 10:17 PM
  • I know this post is really old but I just hope this will get caught by searching engines ...

    I got the same problem : VBA Replace() and Trim() can't identify spaces in a MS Word FormField object. Asc() function returns 32. When I converted in Bytes, I realized that the spaces where combinations of characters 2 (STX) and 32 (SP) instead of 32 (SP) and 0 (NUL).

    So here's my "solution" to the problem :

    Sub TrimPhantomSpaces()

        Dim pstFFvalue As String
        Dim b() As Byte
        Dim i As Integer
       
        ' Get the string from current Word FormField
        pstFFvalue = Selection.Bookmarks(1).Range.Text
       
        ' See 2 and 32 characters for spaces (Odd STX + SP)
        b = pstFFvalue
        For i = 0 To UBound(b)
            Debug.Print b(i)
        Next
       
        pstFFvalue = StrConv(StrConv(pstFFvalue, vbFromUnicode), vbUnicode)
       
        ' See 32 and 0 characters for spaces (Usual SP + NUL)
        b = pstFFvalue
        For i = 0 To UBound(b)
            Debug.Print b(i)
        Next
       
        pstFFvalue = Trim(pstFFvalue)

    End Sub

    Thursday, June 21, 2018 2:57 PM