Character Interpretation

Hi srinivas_vemla,

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

I hope this helps.

jaffer

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

you better take away this apostrophes(') ,
so on Before Update you can use this code:
[YourFieldName]=replace([YourFieldName],"'","")

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"

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

OR if you want to replace it with a double quote, then use:

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

we use Access 2000, and we got this issue too

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.

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.

Right on the money GRayL,
I just had this issue on a project I am working on.

jaffer

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

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