MS Access Hates The Irish?

Factual Questions
1

I am trying to program an Access database, with pretty good results, except that any time you enter a name like O’Leary, O’Hanlon, O’Reilly, it causes programming errors.
I know the cause is the Apostrophe in the name - it is code and screws everying up. Removing the apostrophe fixes it right up…however, short of having to write all the names OLeary, OHanlon, OReilly…is there a way to override code and allow the apostrophe in the names?
I looked through the MS Office help desk archive, and they were not very helpful.
So I figure, where else but here to find the wisest answers from sage readers.
Thanks!

2

You need to replace a character in the string, use the routine in this link to help you out.
http://www.mvps.org/access/strings/str0004.htm

3

MS Access does have some problems with certain characters in text. What you could do is store the part of the name after the “O’” and have a boolean indicating whether or not an “O’” should be placed in front of it for display (I think you can have a query do that in Access SQL).

4

You couldn’t pick a less inflammatory topic? :slight_smile:

This is an extraordinarily common newbie problem. It’s also common to anyone new to string manipulation. You may find the following resources helpful in your learning process:
Dev Ashish’s Access Web
comp.databases.ms-access
Access 2000 Developer’s Handbook

Additionally, there are several help topics within Access itself. Search on “quotations in expressions”, “quotation marks in strings”. You should find an answer in short order.

The short and simple answer is to replace the single apostrophe with two before allowing it to be processed by your code.

5

God I’m glad I’m a FileMaker programmer and not an Access programmer!

6

Oh, so YOU’RE the one! :slight_smile:

7

Ach du scheisse…

I took two years of German and it was a snap compared to the little “string” tidbits being referred to above.

Thanks to all for your kind attempts to help, but I was sorta hoping to get a nice “enter (’) on line 14” response, not Boolean logic and NATO missile encryption codes.

I think I’ll just go out and tip Mrs. OLearys cow and call it a day.

Thanks anyway!

8

The simple answer is to double the single-quotes to keep them from being treated like a delimiter. For example,

SELECT * FROM tblNames WHERE LastName=‘O’Leary’
SELECT * FROM tblNames WHERE LastName=‘O’‘Leary’

The first is a syntax error. The second one will work properly. Note that the second example uses two single-quotes, not one double quote.

Depending on the language you’re using to generate the SQL queries, it may be trivial to double them up. In VBScript, you’d use something like
lastname = Replace(lastname, “’”, “’’”)
and then concatenate that in your string. In Perl, it’d be a simple substitution like
lastname =~ s/’/’’/g;

This issue isn’t unique to Access. Any language that uses SQL has to properly account for single quotes in the data fields, and any language that uses delimiters has to account for including those delimiters in data strings.

9

Just to make it a little clearer, I’ll put micco’s example in [code][/code] tags:


SELECT * FROM tblNames WHERE LastName='O'Leary'
SELECT * FROM tblNames WHERE LastName='O''Leary'