Solved

Character Interpretation

Posted on 2004-10-29
483 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
Question by:srinivas_vemla
    13 Comments
     
    LVL 27

    Expert Comment

    by:jjafferr
    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
    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
    you better take away this apostrophes(') ,
    so on Before Update you can use this code:
    [YourFieldName]=replace([YourFieldName],"'","")
    0
     
    LVL 50

    Expert Comment

    by:Steve Bink
    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
    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
    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
    we use Access 2000, and we got this issue too
    0
     
    LVL 5

    Expert Comment

    by:niblick
    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
    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
    Right on the money GRayL,
    I just had this issue on a project I am working on.

    jaffer
    0
     

    Author Comment

    by:srinivas_vemla
    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
    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 50

    Accepted Solution

    by:
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    Title # Comments Views Activity
    VBA Query won't Edit 8 27
    Linked Excel Spreadsheet 13 43
    Message box Yes/No on Access 2010 subform 6 21
    Windows 7 - Fuzzy Fonts 11 15
    The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    877 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now