Getting 'Smart Quotes' out of Access

Every week I export a txt file of a table out of Access to a third party
provider who converts it to an XML file for wide distribution and then upload
to various websites such as Amazon. I am having problems with lots of
characters being converted improperly, most significantly the apostrophes and
quotation marks. Is there a way I can 'clean' these out of my database (use a
different font such as Arial Unicode in the table?) or when I export the txt
file?

"Mark H" wrote in message
...
Every week I export a txt file of a table out of Access to a third party
provider who converts it to an XML file for wide distribution and then
upload
to various websites such as Amazon. I am having problems with lots of
characters being converted improperly, most significantly the apostrophes
and
quotation marks. Is there a way I can 'clean' these out of my database
(use a
different font such as Arial Unicode in the table?) or when I export the
txt
file?

You can change the table font. Open the table, select the data,by either
selecting all the column, or rows, or by clicking on the intersection of
them in the opper left corner, now go to Format Font and choose the font
you wish. I don't know if this will solve your problem though.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

"Arvin Meyer [MVP]" wrote in message
...
"Mark H" wrote in message
...
Every week I export a txt file of a table out of Access to a third party
provider who converts it to an XML file for wide distribution and then
upload
to various websites such as Amazon. I am having problems with lots of
characters being converted improperly, most significantly the apostrophes
and
quotation marks. Is there a way I can 'clean' these out of my database
(use a
different font such as Arial Unicode in the table?) or when I export the
txt
file?

You can change the table font. Open the table, select the data,by either
selecting all the column, or rows, or by clicking on the intersection of
them in the opper left corner, now go to Format Font and choose the
font you wish. I don't know if this will solve your problem though.

Unless there's a reason for keeping them in the text, you might try running
an Update query to change them permanently.

The ASCII value for an opening double smart quote is 147, a closing double
smart quote is 148. An opening single smart quote is 145, a closing single
smart quote is 146. That means you can use:

UPDATE MyTable
SET MyField = Replace(Replace(Replace(Replace(MyField, Chr(147), Chr(34)),
Chr(148), Chr(34)), Chr(145), Chr(39)), Chr(146), Chr(39))

Alternative, use that awful Replace in a Select query.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Douglas J. Steele" wrote in
:

"Arvin Meyer [MVP]" wrote in message
...
"Mark H" wrote in message
...
Every week I export a txt file of a table out of Access to a
third party provider who converts it to an XML file for wide
distribution and then upload
to various websites such as Amazon. I am having problems with
lots of characters being converted improperly, most
significantly the apostrophes and
quotation marks. Is there a way I can 'clean' these out of my
database (use a
different font such as Arial Unicode in the table?) or when I
export the txt
file?

You can change the table font. Open the table, select the data,by
either selecting all the column, or rows, or by clicking on the
intersection of them in the opper left corner, now go to Format
Font and choose the font you wish. I don't know if this will
solve your problem though.

Unless there's a reason for keeping them in the text, you might
try running an Update query to change them permanently.

The ASCII value for an opening double smart quote is 147, a
closing double smart quote is 148. An opening single smart quote
is 145, a closing single smart quote is 146.

Those are the ANSI values. For instance, ASCII 148 is the o with
umlaut.

I'm very confused here, as when I open Access, Chr(148) does return
a curly quote, and Asc("”") (that's a curly close quote in the
center returns 148. It surprises me that the Asc() function returns
an ANSI value. The help file is silent on the distinction between
ASCII and ANSI encoding, and just says "character code."

That means you can use:

UPDATE MyTable
SET MyField = Replace(Replace(Replace(Replace(MyField, Chr(147),
Chr(34)), Chr(148), Chr(34)), Chr(145), Chr(39)), Chr(146),
Chr(39))

Alternative, use that awful Replace in a Select query.

....which is to say that your recommendation works, but your
terminology was inaccurate.

BTW, I've contemplated for years creating a Replace function that
would accept an array for both the find and replace arguments, as is
the case in PHP, but have never quite gotten round to it. It would
look something like this:

ODQ = Chr(147)
CDQ = Chr(148)
OSQ = Chr(145)
CDQ = Chr(146)
DQ = Chr(34)
SQ = Chr(39)
strResult = aReplace([InputField], _
Split(ODQ & ",” & CDQ & "," & OSQ & "," & CSQ,","), _
Split(DQ & "," & DQ & "," & SQ & "," & SQ,",")

Or another way:

Dim strFind(3) As String
Dim strReplace(3) As String

strFind(0) = Chr(147)
strFind(1) = Chr(148)
strFind(2) = Chr(145)
strFind(3) = Chr(146)
strReplace(0) = Chr(34)
strReplace(1) = Chr(34)
strReplace(2) = Chr(39)
strReplace(3) = Chr(39)
strResults = aReplace([InputField], strFind(), strReplace())

Obviously, you couldn't do this in a query, where the first solution
would work better, but this kind of thing in code would be more
useful, particularly if you have to do multiple replace operations
with the same find/replace pairs.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On 19 May 2010 20:04:18 GMT, "David W. Fenton"
wrote:

I'm very confused here, as when I open Access, Chr(148) does return
a curly quote, and Asc("”") (that's a curly close quote in the
center returns 148. It surprises me that the Asc() function returns
an ANSI value. The help file is silent on the distinction between
ASCII and ANSI encoding, and just says "character code."

Well, you know the old saying - ASCII a silly question, get a silly ANSI!
--

John W. Vinson [MVP]

"Arvin Meyer [MVP]" wrote in
:

You can change the table font. Open the table, select the data,by
either selecting all the column, or rows, or by clicking on the
intersection of them in the opper left corner, now go to Format
Font and choose the font you wish. I don't know if this will
solve your problem though.

Fonts don't change character encoding, so this can't possibly solve
the problem. I don't know why you keep suggesting this. The problem
described is a mismatch between source and destination character
encodings. Resolving that mismatch the only thing that's going to
definitively solve the problem.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

John W. Vinson wrote in
:

On 19 May 2010 20:04:18 GMT, "David W. Fenton"
wrote:

I'm very confused here, as when I open Access, Chr(148) does
return a curly quote, and Asc("”") (that's a curly close quote in
the center returns 148. It surprises me that the Asc() function
returns an ANSI value. The help file is silent on the distinction
between ASCII and ANSI encoding, and just says "character code."

Well, you know the old saying - ASCII a silly question, get a
silly ANSI!

*GROAN*

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"David W. Fenton" wrote

Well, you know the old saying -
ASCII a silly question, get a
silly ANSI!

*GROAN*

I'm sure that John will take much pleasure in your response. As a career
punster, he knows that if it doesn't elicit a "groan", it can't possibly be
a great pun.

Larry
Access newsgroup support is alive and well in USENET
comp.databases.ms-access