Jet SQL RTRIM Doesn't work 1

[dobe1]

I need to remove padded spaces from several fields, and the Trim(Name) and RTrim(Name) does not remove the spaces. Any suggestions?

 

[dobe1]

BTW, this is Access 2003

 

[Remou]

Where did you get the data from? The spaces may not be the ordinary space character (32). It would be worth checking, something on these lines may suit:

Dim rs As DAO.Recordset

Set rs=CurrentDB.Openrecordset("NameOfTable")
For i=0 To Len(rs!FieldNameHere)
   Debug.Print Chr(Mid(rs!FieldNameHere,i,1))
Next

http://lessthandot.com

 

[dobe1]

COMBS ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ

I brought this data into my GIS, and found the padding actually to be the series of "y's" with a colon on top.
How do I get rid of this.

 

[dhookom]

You could try copy one of the ÿ into the clipboard and then do a search and replace to replace with null (no value).

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

That should have been:

For i = 1 To Len(rs!FieldName) - 1
   Debug.Print Asc(Mid(rs!FieldName, i + 1, 1))
Next

You could use similar code to replace the character (255), but DHookom's idea is probably easier.

http://lessthandot.com

 

[Remou]

*sigh*

For i = 1 To Len(rs!FieldName)
   Debug.Print Asc(Mid(rs!FieldName, i, 1))
Next

http://lessthandot.com

 

[eerich]

Remou,

Is there a way to identify the chr using a straight sql statement vs using the code function you've listed?

Thanks

 

[HarleyQuinn]

If the data is always padded to the far right with the characters (or the furthest right character is a Space) then in your Field box try using something like:

Trimmed: Trim(Replace([YourField],Right(Trim([YourField]),1)," "))

That will take to rightmost character of a trimmed version of your field, replace it with a space and then trim the result.

Hope this helps

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[Remou]

Another couple of ideas:

Replace one character:

UPDATE Table A SET A.Field1 = Replace([Field1],Chr(255),"");

Delete everything from the first ÿ to the end of the line:

UPDATE Table A SET A.Field1 = Left([Field1],Instr([Field1],Chr(255))-1)

You may run into problems if your field does not allow zero-length strings or if any of the fields are null, but it is possible to work around that.

http://lessthandot.com

 

[dobe1]

I just tried the above and it did not work.

What I did do that worked was:

update table set field = mid(field1,1, instrrev(field1," ")-1)

fortunately there was a space just before the "wierd Y's".

I will try your (the above) code again. It is one I would like to keep in my library, if I can get it to work.

Thanks

 

[dhookom]

If this is a one time shot, you can try
- open the table in datasheet
- place your cursor in the field
- select Edit-> Replace
- hold the alt key and enter 0255 in the Find What
- leave the Replace With blank
- set the Match to any part of the field
- Look in either the field or table
- click Replace All

Duane
Hook'D on Access
MS Access MVP

 

[dobe1]

Thanks, but this will not be a one time shot.