Removing unseen characters from field

[RicoU]

Hi,

Is there a way to remove unseen characters from a field using an update query? I can't see the characters in the actual field in the Access table. However, if I copy the field and paste it into TextPad there seem to be left and right quotation marks scattered through the values in the field in question.

Thank you in advance.

[RicoU]

I should add that the field is a LongText format and does legitimately contain question marks, brackets, colons, and periods.

[Micron]

Research how to use the Replace function and give that a try in your update query.
Not sure what you mean by left and right quotation marks. Those are slanted ones? Replace would need those exact characters, or what might also work is the Ascii code for them instead of the actual character. Also, it seems that you will need to nest 1 Replace function expression in another one to do it in one pass, or you'll need 2 queries - 1 for left and 1 for right character.

[RicoU]

I tried this in an update query because the Chr(34) represents quotation marks, but it didn't seem to remove them. When I run the query, I get the message that 155 records have been updated.

Replace([table_name]![qtext],Chr(34),"")

Sorry for the confusion about right and left quotation marks. The marks look the same. In some of the records, the quotation mark appears at the start of the text in the field. In other records, the quotation mark appears at the end of text.

[CJ_London]

perhaps show some example text as it is and how you want it to appear

if your longtext is set to richtext, there may well be html code used for formatting which may contain or replace "

the message doesn't mean that 155 records have been 'corrected' just 155 records have been processed

[Micron]

OK, so they're not the slanted ones then. I just tried your expression on a table with 33 records, only 1 which contained "cat". It also seemed to effect all the records but it did remove the quote marks. Now I have just cat. Perhaps yours are not standard quote marks, so not Chr(34). You could try copying & pasting to the immediate window and ask what they are, as in

?Asc("characterpastedhere") You may need to double them up as in ?Asc("""")

and see what number you get. Why the domain reference? Is there more than one table in this update query? If not, try with only the field name.
Also, you did refresh the table view if you already had it open?

[RicoU]

Mircon,

Thank you for the suggestion. They come back as 34. Found a nice website: http://asciivalue.com/ to help me with that


I'll remove the domain reference since only one table is involved.

Yes, I did close the table and re-open it after running the query.

[RicoU]

Ajax,

The text format is set to "Plain text" and the text align is set to "General" for the field in question.

[Micron]

As Ajax says, it could actually be an entity code (&#34). If still not working, try to copy/paste the entire string, including any hidden characters. You might have to create a form with a textbox and set its control source to that field. Then you can use the immediate window to get the full string, assuming there is more)
e.g. ?forms!formNameHere.textboxNameHere

EDIT - just saw your last post. This isn't making sense. Originally you wrote that the characters are hidden. The can't be hidden if they're plain text. Your control might be set to plain text format but your table field is rtf format. That control setting should hide rtf characters but they can still be in the field, which is what you'll end up with when you export/copy the data.