• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

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
0
Fordraiders
Asked:
Fordraiders
  • 3
2 Solutions
 
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
 
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
 
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now