Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011

    Unanswered: Remove Spaces to Right of String

    I know this question (or something simliar) has been asked and answered. I an Access DB that I have just started working with. THere are records that have different amounts of spaces at the end of a string. I have tried Trim and Replace and neither work. Any additional suggestions?

  2. #2
    Join Date
    Oct 2009
    I need to ask; how is it that Trim does not work?

    I don't mean to be snarky...but it is the function of the Trim method to do exactly what you seek....and so I'm not clear as to why it would not be working.
    www CahabaData com

  3. #3
    Join Date
    Sep 2011
    That is why I posted, the trim() did not work . What I think I have figured out is that what appear as blank spaces are really characters that are either unrecognizable or invisible to Access (maybe enters). The data set is an excel download from Cognos.

    So, back to my original question, any suggestions how to delete data to the right of the string that I can see?


  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    consider using the rtrim function

    or if they are not spaces then examine the data to find out what symbols they are may need to write a quick and dirty fucntion to analyse the symbols use the ASC function to ding out the ASCII/ANSI symbol number
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Provided Answers: 14
    I would first try to determine what precisely are those invisible characters:
    For i = 1 To Len(Field1)
        Debug.Print Mid(Field1, i, 1), Asc(Mid(Field1, i, 1))
    Next I
    If it's always the same character, say Chr(255), the solution is rather simple, though it can be slow:
    FieldValue = Left(Field1,Instr(Field1, Chr(255)) -1)
    FieldValue = Replace(Field1, Chr(255), "")
    The problem would become more complex if Chr(255) is used everywhere in lieu of Chr(32).
    Have a nice day!

Posting Permissions

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