Extra spaces not removed by Trim or Replace functions

[Euler271]

I have text fields in a table that for some reason aren't responding to the Trim or Replace functions. I'm trying to remove the trailing spaces after the text but whatever the length of the text it pads the field with enough spaces to make 255 and I can't get rid of them. I enter the text using SQL from another table that I imported from an Excel workbook.

Has anyone run into this before? Any ideas?

[orange]

Please post an example o the text and the routine you're using to Trim it/them.
Are you certain they are spaces?

[ArviLaanemets]

Are data copy-pasted or exported from some web site? There often are used non-ASCII characters/UNICODE characters, which look like normal ones, but have different code, and for any database are not same as ASCII characters you enter when typing. Copy some such "space" into Excel, enter normal space into another field, and use CODE() function on both fields - is the code returned same for both spaces?

[Euler271]

I didn't understand your post. I assume that they are spaces after the text.

[Euler271]

UPDATE tblTemplate SET tblTemplate.Field1 = Replace([Field1]," ","");
UPDATE tblTemplate SET tblTemplate.Field1 = Trim([Field1]);

[Euler271]

I copied some of the extra space into Excel and used the Code function. It returned 32, indicating spaces.

[davegri]

If the target field is a string (and it must be if it has any spaces), this example will work. Both updates do the same thing, except the replace will also eliminate embedded spaces.

Private Sub cmdClean_Click()
    Dim sSQL As String
    sSQL = "UPDATE tblInformation SET tblInformation.CustomerName = '" & Replace([CustomerName], " ", "") & "';"
    CurrentDb.Execute sSQL, dbFailOnError
    sSQL = "UPDATE tblInformation SET tblInformation.CustomerName = '" & Trim([CustomerName]) & "';"
     'Debug.Print sSQL
    CurrentDb.Execute sSQL, dbFailOnError
    Me.Requery
End Sub

[Euler271]

Thank you for that. I tried using a loop structure that tests the ASCII value of each element of the string and if it's a letter then it adds it to a new string. Otherwise, it doesn't add the character. I then add the new string to a new field.

[davegri]

I tried using a loop structure

That approach should work, and it's a good exercise in code structure.
Not a good approach if you have a large number of records to process, as it will take some time.

[June7]

In my experience, Access automatically drops trailing spaces. So some other character would have to follow the spaces in order for them to be retained which in this case must be non-printing since you can't see it.

[orange]

@Euler,
Can you post a copy of the database with a few records to show the problem?

[ArviLaanemets]

I copied some of the extra space into Excel and used the Code function. It returned 32, indicating spaces.

Copy the whole string, and get the code for rightmost character of string.

[Euler271]

Thanks to all who helped. Interestingly, I found that when I programatically created a new table using SQL ("CREATE TABLE") and copy records to it the problem happens. But when I create the same table in DAO ("CreateTableDef") I don't get the problem. I hope this will help others with this frustrating issue.