Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Character Interpretation

Posted on 2004-10-29
16
Medium Priority
?
540 Views
Last Modified: 2013-12-03
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
0
Comment
Question by:srinivas_vemla
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +3
16 Comments
 
LVL 27

Expert Comment

by:jjafferr
ID: 12445685
Hi srinivas_vemla,

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

I hope this helps.

jaffer
0
 
LVL 3

Expert Comment

by:MBarber1957
ID: 12445700
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 12445705
you better take away this apostrophes(') ,
so on Before Update you can use this code:
[YourFieldName]=replace([YourFieldName],"'","")
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 51

Expert Comment

by:Steve Bink
ID: 12445707
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
 

Author Comment

by:srinivas_vemla
ID: 12445724
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 12445726
OR if you want to replace it with a double quote, then use:

[YourFieldName]=replace([YourFieldName],"'",chr(34))
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12445733
we use Access 2000, and we got this issue too
0
 
LVL 5

Expert Comment

by:niblick
ID: 12446971
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
 
LVL 44

Expert Comment

by:GRayL
ID: 12453432
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 12453448
Right on the money GRayL,
I just had this issue on a project I am working on.

jaffer
0
 

Author Comment

by:srinivas_vemla
ID: 12474002
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 12474153
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
 
LVL 51

Accepted Solution

by:
Steve Bink earned 1600 total points
ID: 13134043
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question