Kill the " ' " in O’Donnell

J

JBurlison

Registered User.
Local time
Today, 14:51
Joined
Mar 14, 2008
Messages
172
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
 
Code: 
select replace(lname,"'","") from mytable;
In SQL

or

Code: 
myString = Replace(myString,"'","")
In a VBA subroutine/function.
 
Shouldn't you be correcting the program rather than fudging the Data?

What happened when you tried Replace?

Brian
 
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
 
I have vague memories of a thread tackling this , I'll see if I can find it.

Brian
 
chr(34) at each end of the name should sort out the code
 
Will this work:

Code: 
"""O'Reilly's"""
 
Will this work:

Code: 
"""O'Reilly's"""
 
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 "'".
 
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
 
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.
 
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
 
FWIW, there's a blurb about handing special characters in Jet, listing three different ways to do that.

georgedwilkinson said:
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 "'".
Click to expand...

You mean to use parameter queries in place of dynamic SQL? Yeah, that would be definitely easier and more "correct".
 
Brianwarnock said:
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
Click to expand...
i read it but its referring to quotation marks (Quotation mark = " ) and my problem is with apostrophe (apostrophe = ' )
 
JBurlison said:
i read it but its referring to quotation marks (Quotation mark = " ) and my problem is with apostrophe (apostrophe = ' )
Click to expand...
Similar rules apply to both. ie you can have a single apostrophe within quotations or a single quotation within apostrophes.
 
You did not read it very well, the offending string was John's

Brian

Quotation mark = ' same as apostrophe
Double qotation marks = "
 
Last edited:
Banana said:
You mean to use parameter queries in place of dynamic SQL? Yeah, that would be definitely easier and more "correct".
Click to expand...

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:
Code: 
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.
 
George, that's what I'd think parameter query does.

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

This also can be set in VBA with this construct:

Code: 
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....
 
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).
 
You must log in or register to reply here.

Similar threads

A
Glitch in Replace function / Object Browser documentation
Replies
6
Views
225
isladogs
isladogs
C
File Dialog with Default Value that has Spaces
Replies
10
Views
299
CedarTree
C
N
Solved Receiving Emails in Access Database using CDO 2000 library
Replies
10
Views
347
sonic8
S
A
Solved Kill working manually but not in code
Replies
19
Views
696
Alc
A
A
Find and Replace
2
Replies
23
Views
733
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom