weird character will not disapper after trim update

Posted on 2011-10-06
Last Modified: 2012-08-14
access 2003
need a function if necessary please...or update statement

I have a field in a table that has a box character at the end of the field.

I tried using:
UPDATE SapAlt_Staging_Prmdb025 SET SapAlt_Staging_Prmdb025.alt_acc_material_no = Trim([alt_acc_material_no]);

but the character will not go away ?
see pic below


Question by:fordraiders
    LVL 119

    Expert Comment

    by:Rey Obrero
    you can not remove that weird character with a trim()

    if you want the first 6 characters, use


    update table
    set [fieldName]=left([fieldName],6)  
    LVL 119

    Expert Comment

    by:Rey Obrero
    you can also use the replace() function, but you have to know the ascii value of thet weird character.

    try this

     update table
    set [fieldName]=replace(replace(replace([fieldName],chr(13) & chr(10),"") ,chr(13),""),chr(10),"")
    LVL 119

    Accepted Solution

    try this

     update table
    set [fieldName]=replace(replace(replace(replace([fieldName],chr(13) & chr(10),"") ,chr(13),""),chr(10),""),chr(9),"")
    LVL 26

    Assisted Solution

    That weird character looks suspiciously like a carriage return
    In a query with that field throw this in a column

    LastChar: Asc(Right([alt_acc_material_no],1))

    That'll give you the Chr() number for that character.
    Once you've got it, then
    UPDATE SapAlt_Staging_Prmdb025 SET SapAlt_Staging_Prmdb025.alt_acc_material_no = Replace([alt_acc_material_no],Chr(WhateverThatChrNumberIs),"");
    Ought to work when you replace WhateverThatChrNumberIs with the value you discovered earlier.
    LVL 3

    Author Closing Comment

    Thanks to both

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now