Character Interpretation

Hello All,
We are migrating our Access application from desktop to Terminal Server while also moving from Windows 95 to Windows 2000 clients. Our backend is Oracle 8173. We have changed our character setting on Oracle for some reasons.
Sometimes users copy text from MS Word and paste it in Access Text fields. This text can also contain apostrophes('). This used to work fine in Windows 95 version. But now, such apostrophes throw special characters, instead of normal '. Any idea why this is happening? Is it something to do with the character setting on Oracle or is it Access issue?

Your suggestions are highly appreciated.

Thanks,
Srinivas
srinivas_vemlaAsked:
Who is Participating?
 
Steve BinkCommented:
I hate selecting my own answers, and my comment was essentially ignored when it was posted, but I still maintain it is correct.  I KNOW this is documented, but I guess I just don't know what to put in to the search engine to find it again.  This is one piece of information that corroborates my theory, but it does not apply specifically to databases.

From:  http://home.earthlink.net/~bobbau/platforms/specialchars/

"[...] For example, if a page contains a curly Windows apostrophe in the form of a numeric reference (’), Netscape 4.x for UNIX does display an apostrophe. If a page contains a Windows apostrophe in the form of a raw 8-bit code (’), Netscape 4.x for UNIX displays a question mark."

Here's another that describes the same problem in the context of MS Word...unicode translation:

https://www.gradrec.dfat.gov.au/gradrec/GRConfig.nsf/vHelp/disablequotes

And one more that describes the issue with pages that look fine locally, but have the replacement characters when served from the web:

http://www.officekb.com/Uwe/Forum.aspx/publisher-webdesign/1287/bullets-apostrophes-replaced-by-question-mrks

I am positive there is more information out there, since I had to hear about it somehow.  Many of the pages I've searched have commented that the ASCII representation of the apostrophe is being phased out in favor of Unicode characters, but since Unicode is not yet as widely supported as it should be this leads to interpretation issues.  User education is also blamed for the persistence of the ASCII version, which is translated differently among different Unicode pages.

I've seen the apostrophe used for code-page designation mostly in MSSQL, and have never had an issue with it myself, but I program in homogenous environment most times.  For the DB2 platform, this same issue is resolved not by doubling the apostrophe (like we do in Access), but by replacing it with "\'", which indicates the literal apostrophe character.
0
 
jjafferrCommented:
Hi srinivas_vemla,

Its an Access issue, it was refused in one of my fields.

I hope this helps.

jaffer
0
 
MBarber1957Commented:
This brings back memories of porting an Access 95 database to Access 97. One user had a huge database containing strings surrounded by single quotes. The only fix was to convert all single quotes to double quotes. Sorry about the brief answer, got to go ....
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jjafferrCommented:
you better take away this apostrophes(') ,
so on Before Update you can use this code:
[YourFieldName]=replace([YourFieldName],"'","")
0
 
Steve BinkCommented:
Are you also updating your version of Access?

The apostrophe character can be interpreted as a code-page identifier in many database platforms, including Access.  You can usually get around this by replacing a single apostrophe with two apostrophes:   "don't" becomes "don''t"
0
 
srinivas_vemlaAuthor Commented:
Hello Jaffer,

Thanks for your reply.

But can you be a little more specific on it. I am not sure how this can be an Access issue. Is it because Access 97 interpretation differs from Access 2002's (not sure if it differs too).

Thanks,
Srinivas
0
 
jjafferrCommented:
OR if you want to replace it with a double quote, then use:

[YourFieldName]=replace([YourFieldName],"'",chr(34))
0
 
jjafferrCommented:
we use Access 2000, and we got this issue too
0
 
niblickCommented:
I assume that the users are not pasting directly into the table but via some form.  If so the problem is an SQL problem, not Access or Oracle.  I think you will find that the problem fields are updated into the database via an SQL statement.  The single quote throws off SQL statements that is why you need to use the 'replace' method that is described below.  If the field is directly connented to the table field you will not have this problem becaue a sql statement is not being issued to update the data.
0
 
GRayLCommented:
You cannot use the replace() function directly in a query in Access 2000.  It must be embedded in code and called from a query.

Public Function RepChar(myStr As String) as String

RepChar = Replace(myStr,"'",Chr(34)

End Function

Call the function thus:

Update myTable Set myFld = RepChar(myFld);

This will replace all single quotes in myFld in myTable with a double quotes.
0
 
jjafferrCommented:
Right on the money GRayL,
I just had this issue on a project I am working on.

jaffer
0
 
srinivas_vemlaAuthor Commented:
Thank you guys for all the answers...

I use a VBA form to load the data into database. But the issue looks a bit different. When I paste the text from MS Word and save it using the form, there is no error generated and the save process completes successfully. But when I re-open the form, the apostrophe is shown as a question mark.

I assume that Microsoft Word adds some meta stuff to the apostrophe that we type-in. Say the statement: This is Srinivas's test.. the apostrophe after srinivas is more than the keyboard apostrophe in MS Word. (It looks more angle'd). So how is this special apostrophe stored in MS Access tables or the Oracle tables? Doesn't this storage also depend on the character setting present on the database, which would say, the special apostrophe should be interpreted in a way which might be different from an apostrophe itself.

Please correct me if I am wrong.

Thanks,
Srinivas
0
 
jjafferrCommented:
I don't think it makes a diffence weather you catch the apostrophe at paste time OR later,
the point here is, you have to change it either way,
Now, if we don't know what Word saves, then we can check what ASCI Character number it is, like this:

if you paste the Word contenets into Field [MyField] in Access Form, this is how you can know the characters of each letter:
for i=1 to len([MyField])
myChar=mid([MyField],i,1)
msgbox " the Original Letter is: *****" & myChar & "***** and the ASCI Character number of it is: " & asc(myChar)
next i

Then once you know the ASCI character number, you can use the replace statement, like this:
myChar=asc(chr(xxx)) ' where xxx is the character number of that character
[myField]=replace([myField],myChar,chr(34))

OR

you can use the replace statement as discussed in the earlier posts,
GRayL gave you the right way to take to update the Table.

jaffer
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.