weird character will not disapper after trim update

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

Thanks
fordraiders


weird-character.png
LVL 3
FordraidersAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
try this

 update table
set [fieldName]=replace(replace(replace(replace([fieldName],chr(13) & chr(10),"") ,chr(13),""),chr(10),""),chr(9),"")
0
 
Rey Obrero (Capricorn1)Commented:
you can not remove that weird character with a trim()

if you want the first 6 characters, use

left([fieldName],6)  


update table
set [fieldName]=left([fieldName],6)  
0
 
Rey Obrero (Capricorn1)Commented:
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),"")
0
 
Nick67Commented:
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.
 
0
 
FordraidersAuthor Commented:
Thanks to both
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.