Escaping punctuations and quotes

[techbuddha]

Hi I am new to VB. I have an access database that has data that uses the single quote like in (don't) or (g'night).

I have create a procedure that will do a search and replace, Which I thought should be simply search for (') and replace it with (& "'" &), but that just leads to a billion (& "'" &) of these being inserted. Okay maybe it's not that much but it is a lot.

Isn't there an easier way to get around "data" that uses characters that produce errors in "code". I saw an example in Perl on this site but I don't get it how to translate that into VB.

many thanks from the "geek in training"

[Killer42]

I don't quite follow. What sort of conversion would you like to do?

[techbuddha]

I don't quite follow. What sort of conversion would you like to do?

When I run the current script and one record has a field that has a single quote in it . I get an error. I am assuming the VB is expecting and matching single qoute. My question basically, I guess, is how do encapsulate the offending character OR what kind of substitution can I do for the offending character that will prevent the error.

The error occurs when I am doing an insert and the offending value looks something like this ( b'ad cha'ract'er). But usually it is just one ' .

I don't want to change the data the user is entering. I want them to enter the data the way they get it or the way the word is spelled. I just don't want words like M'duqes to give me an error cause there is no matching single quote .

so no transformation. more like hmmm...... chameleoning..... masqurading ..... character deception possibly ?? ;-)

right now my work around is to substitute ' with `. The later is whatever that thing is next to the #1 key in the upper left hand corner, but I think I am going down the wrong road when I start altering the data to fit the code instead of writing the code to fit the data. Not so???

[Killer42]

Hm... well, I did read something recently about being able to delimit strings in SQL with # instead of '. Perhaps that would help. You'll probably find more help for this in the Access forum, I think.

Actually, there is an entry in their tips and tricks covering apostrophes. Perhaps it will help. You can find it in their T&T index, here. Just use the text-search function of your browser (or your own eyes, of course) to scan for "Quotes".

[kentgorrell]

In Jet (Access) you should be able to contain a string between double quotes - three each side. eg
strSQL = "SELECT * FROM tblNames WHERE Last_Name = """ & strName & """;"
You see when you use the three quotes you end up with one. You may like to see the result by inserting debug.print strSQL to print the string to the immediate window. it should look like
SELECT * FROM tblNames WHERE Last_Name = "O'Reilly";

[Killer42]

In Jet (Access) you should be able to contain a string between double quotes - three each side. eg
strSQL = "SELECT * FROM tblNames WHERE Last_Name = """ & strName & """;" ...

Thanks for that. You can also get this effect by using Chr$(34) for the double-quote character.

[kentgorrell]

You are right CHR$(34) can be easier than trying to keep count of the double quotes.

[Killer42]

You are right CHR$(34) can be easier than trying to keep count of the double quotes.

I think some versions of VB have a named constant you can use, like vbQuotes or something. Can't recall for sure, though. I know I have created one myself a few times, as it makes the code more readable.

[techbuddha]

so then the code could look like this

strSQL = "SELECT * FROM tblNames WHERE Last_Name = chr$(34) & strName & chr$(34);"

[Killer42]

so then the code could look like this

strSQL = "SELECT * FROM tblNames WHERE Last_Name = chr$(34) & strName & chr$(34);"

Not quite. You need to put the value of each variable into the string, not the name of the variable. So your example would be more like...

strSQL = "SELECT * FROM tblNames WHERE Last_Name = " & Chr$(34) & strName & Chr$(34) & ";"

[fplesco]

Not quite. You need to put the value of each variable into the string, not the name of the variable. So your example would be more like...

strSQL = "SELECT * FROM tblNames WHERE Last_Name = " & Chr$(34) & strName & Chr$(34) & ";"

Hi there -

Maybe you can give this code a try:

strSQL = "SELECT * FROM tblNames WHERE Last_Name = '" &  Replace(strName,"'","''") & "'"
 

Basically, just REPLACE a single qoute by 2 single quotes. Same with saving values into the table. Just put REPLACE(<variable or text object>,"'","''")
to every text object or variable that you know, can contain a QUOTE.

[techbuddha]

thanks folks I think I am getting the hang of it ; -)

[Killer42]

... Basically, just REPLACE a single qoute by 2 single quotes ...

Thanks for that, fplesco. In switching around between different programming languages, SQL and so on, I didn't remember that SQL could handle them as doubles like that.

Don't recall whether I mentioned it or not, but I just read recently that you can also use hashes (#) to delimit a string in SQL. That might resolve the immediate issue. Unless it's possible to have hashes in the string, of course - then you're back to square one. :)