Cleaning invisible, unwanted characters from Access table data

[PAGA]

I'm trying to clean text in a large Access 2002 table. It's riddled with old
HTML tags (most of them are easy to remove), Word "smart quotes," and
seemingly dozens of versions of em-space, en-space, single quotes, double
quotes, bullets, etc, most of which appear in Access as dots, bars, and the
like. Is there a "clean up text in Access" tool somewhere out there?

In addition, a Find and Replace operation launched from a form never gets
from one end of the table data to the other. Any number of glitches are
thrown up, as the F&R trips over corrupted records, and stalls. I have a
hunch some of these may be over-long text in Memo fields, some of which, when
I try to chop text out of them, tell me there's so much data in that Memo
field, it can't even be edited.

Is there a way of locating the records causing the trouble so I can delete,
or clean them up to allow F&R to move smoothly through the table data?

Thanks,

Philip

 

[John Nurick]

Hi Philip,

Try using update queries instead of Find&Replace. For instance, this
expression in the Update To: cell of the query design grid will replace
the "left double quote" used by Word to a plain quotation mark:

Replace([MyField],Chr(147),Chr(34))

In SQL view the query would be like this:

UPDATE MyTable
SET MyField = Replace([MyField],Chr(147),Chr(34))
;

 

[PAGA]

John,

This is very useful - thanks.

A hard drive collapse delayed my reading your message, but now it seems I
should find a list of character codes, so I can use your update query to
replace the other nuisance characters which litter my database.

I've tried Access and VBA Help, but there doesn't seem to be a readily
accessible list of characters and their codes -- both the ones I don't want
in my database, and the ones I'd like to replace them with -- along with
descriptions of each, so I can recognise what I'm looking for.

Do you know of a beginner's help list out there somewhere?

Thanks,

Hi Philip,

Try using update queries instead of Find&Replace. For instance, this
expression in the Update To: cell of the query design grid will replace
the "left double quote" used by Word to a plain quotation mark:

Replace([MyField],Chr(147),Chr(34))

In SQL view the query would be like this:

UPDATE MyTable
SET MyField = Replace([MyField],Chr(147),Chr(34))
;

I'm trying to clean text in a large Access 2002 table. It's riddled with old
HTML tags (most of them are easy to remove), Word "smart quotes," and
seemingly dozens of versions of em-space, en-space, single quotes, double
quotes, bullets, etc, most of which appear in Access as dots, bars, and the
like. Is there a "clean up text in Access" tool somewhere out there?

In addition, a Find and Replace operation launched from a form never gets
from one end of the table data to the other. Any number of glitches are
thrown up, as the F&R trips over corrupted records, and stalls. I have a
hunch some of these may be over-long text in Memo fields, some of which, when
I try to chop text out of them, tell me there's so much data in that Memo
field, it can't even be edited.

Is there a way of locating the records causing the trouble so I can delete,
or clean them up to allow F&R to move smoothly through the table data?

Thanks,

Philip

 

[John Nurick]

The Character Map applet charmap.exe is the closest source to your
desktop. But if you do a web search for something like
ISO 8859 western european
you'll find a whole lot more.

ALso, search http://groups.google.com for
function transliterate nurick
and you'll find a function that can change multiple characters in one
go.

John,

This is very useful - thanks.

A hard drive collapse delayed my reading your message, but now it seems I
should find a list of character codes, so I can use your update query to
replace the other nuisance characters which litter my database.

I've tried Access and VBA Help, but there doesn't seem to be a readily
accessible list of characters and their codes -- both the ones I don't want
in my database, and the ones I'd like to replace them with -- along with
descriptions of each, so I can recognise what I'm looking for.

Do you know of a beginner's help list out there somewhere?

Thanks,

Hi Philip,

Try using update queries instead of Find&Replace. For instance, this
expression in the Update To: cell of the query design grid will replace
the "left double quote" used by Word to a plain quotation mark:

Replace([MyField],Chr(147),Chr(34))

In SQL view the query would be like this:

UPDATE MyTable
SET MyField = Replace([MyField],Chr(147),Chr(34))
;

I'm trying to clean text in a large Access 2002 table. It's riddled with old
HTML tags (most of them are easy to remove), Word "smart quotes," and
seemingly dozens of versions of em-space, en-space, single quotes, double
quotes, bullets, etc, most of which appear in Access as dots, bars, and the
like. Is there a "clean up text in Access" tool somewhere out there?

In addition, a Find and Replace operation launched from a form never gets
from one end of the table data to the other. Any number of glitches are
thrown up, as the F&R trips over corrupted records, and stalls. I have a
hunch some of these may be over-long text in Memo fields, some of which, when
I try to chop text out of them, tell me there's so much data in that Memo
field, it can't even be edited.

Is there a way of locating the records causing the trouble so I can delete,
or clean them up to allow F&R to move smoothly through the table data?

Thanks,

Philip