Kill the " ' " in O’Donnell

[JBurlison]

If a “ ‘ “ appears in a person’s name such as “O’Donnell” the program errors.
i need to remove this or replace it with "" (No space) could i use the replace function for this. im not quite sure how this would work

Thx
-Jim

 

[georgedwilkinson]

select replace(lname,"'","") from mytable;

In SQL

or

myString = Replace(myString,"'","")

In a VBA subroutine/function.

 

[Brianwarnock]

Shouldn't you be correcting the program rather than fudging the Data?

What happened when you tried Replace?

Brian

 

[JBurlison]

well i think with the " ' " in there it is thinking im wrapping string such ad a where condition in a dlookup in trying to figure out a way around thin problem id rather not change the data if i do not have to

 

[Brianwarnock]

I have vague memories of a thread tackling this , I'll see if I can find it.

Brian

 

[Brianwarnock]

Try this Thread

Brian

 

[Kempes]

chr(34) at each end of the name should sort out the code

 

[Banana]

Will this work:

"""O'Reilly's"""

 

[Banana]

Will this work:

"""O'Reilly's"""

 

[georgedwilkinson]

This problem is caused by dynamic SQL, which is generally a bad thing. However, it seems to be the standard for most Access VBA programmers. We seem to lack the engineering prowess to use bind variables or, in MS words, "bound parameters".

You can also use Replace(mystuff,"'","''"), I believe (may need one more '), to maintain the integrity of the "'".

 

[tehNellie]

Depending on what is causing the issue, you might also be able to replace a single quote with two single quotes to resolve the issue

 

[JBurlison]

well im not having problems with a quotation mark it the apostrophe in O’Donnell i need that " ' " gone preferably a way for the code to ignore it without having to change the data.

 

[Brianwarnock]

What's the difference between a quotation mark and an apostrophe or is my keyboard short of something?

Did you read the thread I found for you?

Brian

 

[Banana]

FWIW, there's a blurb about handing special characters in Jet, listing three different ways to do that.

This problem is caused by dynamic SQL, which is generally a bad thing. However, it seems to be the standard for most Access VBA programmers. We seem to lack the engineering prowess to use bind variables or, in MS words, "bound parameters".

You can also use Replace(mystuff,"'","''"), I believe (may need one more '), to maintain the integrity of the "'".

You mean to use parameter queries in place of dynamic SQL? Yeah, that would be definitely easier and more "correct".

 

[JBurlison]

What's the difference between a quotation mark and an apostrophe or is my keyboard short of something?

Did you read the thread I found for you?

Brian

i read it but its referring to quotation marks (Quotation mark = " ) and my problem is with apostrophe (apostrophe = ' )

 

[Rabbie]

i read it but its referring to quotation marks (Quotation mark = " ) and my problem is with apostrophe (apostrophe = ' )

Similar rules apply to both. ie you can have a single apostrophe within quotations or a single quotation within apostrophes.

 

[Brianwarnock]

You did not read it very well, the offending string was John's

Brian

Quotation mark = ' same as apostrophe
Double qotation marks = "

 

[georgedwilkinson]

You mean to use parameter queries in place of dynamic SQL? Yeah, that would be definitely easier and more "correct".

No, I mean using what Oracle calls "Bind Variables" and Microsoft calls "Bound Parameters" instead of dynamic SQL. The calling program passes the database the variable along with it's current value. This saves DBMSs the trouble of reparsing, creating a new execution plan, etc. every time it gets the statement. Not sure if it's even possible with an Access back end.

In the Oracle documentation, bind variables are heavily documented and their use is heavily encouraged. Pretty much the same in Java documentation, though not as heavily. In MS documentation, it is difficult to even find references to Bound Parameters and even more difficult to find how to practically implement them. But they do make programming and database performance tuning so much easier for back ends that can use them, regardless of the back end.

You'll know you're seeing non-Oracle bound parameters when you see sql like this:

select * from mytable
where mytable.a = ? AND mytable.b = ?

the "?"s are the bound parameters. You have to bind the parameters to your local variables to get the sql to run.

I think using all this dynamic sql causes more problems than it's worth.

 

[Banana]

George, that's what I'd think parameter query does.

PARAMETERS=MyParam
SELECT * FROM MyTable WHERE MyField = [MyParam];

This also can be set in VBA with this construct:

Dim qdf As DAO.QueryDef
Dim rst as DAO.Recordset

Set qdf=CurrentDb.QueryDefs("NameOfParameterQuery")
qdf.MyParam = SomeData
rst=CurrentDb.OpenRecordset(qdf)

I think people find dynamic SQL easier because they don't have to mess with two objects as above, which IMO Microsoft could have made parameter query easier to implement by having a function to provide the recordset but one could be written.

Now that's a thought... Maybe write a function for everyone to use instead of dynamic SQL... Just pass the query name, parameters and get recordset in return....

 

[georgedwilkinson]

I'll think about that, for what we're discussing, it sounds like you're right on. I'm not sure exactly how Access/Jet works internally but I'm betting that everything internally works a lot like a stored procedure in real DBMSs.

When I had "users" (i.e. Engineers and Scientists) accessing an Oracle database, I just gave them a set of VB functions in modules that they could put into their spreadsheets. I always bound the variables inside the functions so they didn't have to worry about stuff like that and I could keep the DB running efficiently. I've never figured out how to get such efficiencies in Access (yet).