VBA Problem replacing apostrophes

I am trying to use SQL in VBA to replace apostrophe's "'" in a specific fields with a caret "^",, then I run some functions that don't work well with apostrophes, once that is finished I attempt to switch the ^'s back to apostrophes. It is the second part that does not work. The code is as follows:

Function TestApost()
Dim StrQuote As String
Dim StrApost As String
Dim DWstrSQL As String
Dim bVariable As Boolean
Dim strBeginDate As String
Dim strEndDate As String

StrQuote = Chr$(34)
StrApost = Chr$(39)

' Replace apostrophes with Carets

This section works

DWstrSQL = "UPDATE PRTrans_Work SET PRTrans_Work.SubContractorName = Replace([PRTrans_Work]![SubContractorName],Chr(39),Chr(94))"

DoCmd.RunSQL (DWstrSQL)


'Replace all caret's with apostrophes

This section does not work it runs without errors, but the ^'s do not change back to apostrophes

DWstrSQL = "UPDATE PRTrans_Export SET PRTrans_Export.SubContractorName = Replace([PRTrans_Export]![SubContractorName],Chr(94),Chr(39))"
DoCmd.RunSQL (DWstrSQL)


End Function


I have tried a number of things to deal with the single quote issue, but have had no success
Rick RudolphAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee W, MVPTechnology and Business Process AdvisorCommented:
I'm not sure why you want to replace them with carets - just double them -

When using vba, 
Replace(Data, "'", "''")

Open in new window

Lee W, MVPTechnology and Business Process AdvisorCommented:
Yes, SQL has a problem with a single quote mark because that's how it identifies text - unless it's a double single quote mark - then it reads it as a single intended to be part of the text.
Rick RudolphAuthor Commented:
This did not work. The VBA editor  inserted a space before the first apostrophe. When the code was run an error was generated. This is what the line looked like after I made your suggested change:

DWstrSQL = "UPDATE PRTrans_Export SET PRTrans_Export.SubContractorName = Replace([PRTrans_Export]![SubContractorName],Chr(94)," ''")"

Keep in mind that I am trying to build the string and then do the  run SQL command.

So in keeping with my understanding of your post, I tried the following:

DWstrSQL = "UPDATE PRTrans_Export SET PRTrans_Export.SubContractorName = Replace([PRTrans_Export]![SubContractorName],Chr(94)," & "''" & ")"

The debug  string  on this looks like:

UPDATE PRTrans_Export SET PRTrans_Export.SubContractorName = Replace([PRTrans_Export]![SubContractorName],Chr(94),'')

So I think that we are close, just not quite there.

Any more suggestions?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

FREE DOWNLOAD
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm also not sure why you'd replace the ' with ^. It's common to have single quotes in data, and every database engine has methods to deal with them, as Lee pointed out.

If you must, then try this syntax to convert back:

DWstrSQL = "UPDATE PRTrans_Export SET PRTrans_Export.SubContractorName = Replace([PRTrans_Export]![SubContractorName],Chr(94)," & "'" & ")"

Basically, I removed one of the ' in the third argument of the Replace function.

If that doesn't work, try creating a query in the Access Query Design, and see if you can get the syntax right using that. The QDE tends to format your syntax to be Access-safe!
Rick RudolphAuthor Commented:
To follow  up on this. I have tried the suggestions mentioned.

This string works in a query windows:

UPDATE PRTrans_Work SET PRTrans_Work.SubcontractorName = Replace([PRTrans_Work]![SubcontractorName],Chr(94),"'");

The ^ is replaced by a '

This SQL  statement does  not work in VBA code

DWstrSQL = "UPDATE PRTrans_Export SET PRTrans_Export.SubContractorName = Replace([PRTrans_Export]![SubContractorName],Chr(94)," & """" & "'" & """" & ")"

Debug.Print DWstrSQL
DoCmd.RunSQL (DWstrSQL)

Even though the  Debug.Print  displays the following:

UPDATE PRTrans_Export SET PRTrans_Export.SubContractorName = Replace([PRTrans_Export]![SubContractorName],Chr(94),"'")

To my eye the two strings are identical, but the QDE works and the VBA  does  not. Now I know that I can  simply run the query from within VBA, and that will probably work, but I was trying to avoid that.

Does anyone have any fresh insight?
PatHartmanCommented:
You seem to be fixated on changing the apostrophes rather than working with them.  When your data includes apostrophes as it frequently does when the field is a name or notes field, your queries need to use double quotes as the delimiter.

If you can tell us what problem you are having, we are in a better position to help to solve it.  At the moment, we are trying to get your "solution" to work but there may be an easier way.
wsh2Commented:
This will double up (known as escaping) the apostrophes so that your SQL will work.

DWstrSQL = _
    "UPDATE [PRTrans_Export] " & _
    "   SET [PRTrans_Export].[SubContractorName] = " & _
    "'" & Replace([PRTrans_Export]![SubContractorName], "'", "''") & "'"

Open in new window

PatHartmanCommented:
I'm not sure that updating the data to replace the single quotes with two is a good solution.  What happens when someone adds a new record or updates an existing one?  Are you going to have to find all the places where the name field might be updated (including web pages if it comes to that) and modify the code to double the quotes there?  Otherwise, you have just made the mistake of storing a "calculated" value.  The values are potentially wrong immediately after the update query runs.  Then you have the problem of searching for O''Reilly which won't find the O'Reilly which someone just entered two minutes ago.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rick RudolphAuthor Commented:
Thank you for all of your comments. I am going to abandon this question. It works fine within a query, so I am just calling the qdf from the VBA, which works fine.

With regards to just dealing with the apostrophe's, that is probably a good idea, but between the two substitutions, I am running some functions that did not "deal" with the apostrophe's and I have no desire to work on that code.

Thanks again, I will split the points among all who commented.
wsh2Commented:
@PatHartman

When you escape a value, such as we are doing with the apostrophes, only ONE apostrophe is actually stored. The purpose of escaping is to allow the use of a reserved character (the apostrophe) in the data.

@rrudolph  

The VBA code I demonstrated above does work. The reason why your code was proving to be so frustrating is because your SQL syntax was wrong. To place a value into a TEXT field the text has to be enclosed within apostrophes. Eg:

    SET [TextFieldData] = 'My Data'

In the event you want to place an apostrophe into the text field you have to double it up so the SQL interpreter knows that the apostrophe it is encountering is data and not a control character. Eg.

    SET [TextFieldName] = 'Bob O''Brien'

In this example when the data gets stored, the SQL interpreter will remove the extra apostrophe and only place Bob O'Brien into the database.. just like we want it.
PatHartmanCommented:
Wsh2, your update statement is storing two characters.  If you select the text and look at it with an ASCII editor you will see it.  You use the "escape" when you are searching.  so you end up with

Where LastName = 'O''Reilly'

The interior "'" is escaped so it can be embedded in the string.  With Access queries we have somewhat less of a problem since most strings are enclosed by double quotes so we have
"O'Reilly" which doesn't present a problem.  However when we are working with some text that includes double quotes, then we need to switch to using single quotes as the container.

Where SomeLength = '5"'
wsh2Commented:
Please allow me to simplify my VBA code from above and clarify it by replacing all apostorphes (single quote) with Chr(39):
'
'  Initialize
   Dim strName As String
   Dim strSQL As String
'
'  Escape Single Quote (Apostrophe)
   strName = Replace([PRTrans_Export]![SubContractorName], Chr(39), Chr(39) & Chr(39))
'
'  Build SQL statement. String data is enclosed within a quotes.  
   strSQL = _
      "UPDATE [PRTrans_Export] " & _
      "   SET [PRTrans_Export].[SubContractorName] = " & Chr(39) & strName & Chr(39)
'
'  Execute SQL Statement
   DoCmd.RunSQL (strSQL)

Open in new window

 

---------------------------------------
Using your example of 5' (5 feet), and the above code, the finished SQL statement will read:
 
   UPDATE [MyTable] 
          SET [MyTable].[MyLength] = '5'''

Open in new window


Which is what I believe you (@PatHartman) presented as well.

And in this, I do believe we both agree.. {smile}
Rick RudolphAuthor Commented:
Folks,

I really found all of the information quite valuable. The issue that was driving me crazy was that replacing apostrophe's with the caret worked like a charm. Then 10 lines later, the same process in reverse did not work at all.

Thanks again.
PatHartmanCommented:
@wsh2
My point is that you should not be permanently modifying data to solve this problem.  Running a single update query is insufficient because as soon as someone updates/adds a record, the problem could reappear.
@rrudolph
Same comment.  Whether you double the apostrophe or change it to a caret, the problem is that a single update query is not the answer and what you are doing violates normal forms.  You never told us what problem you were trying to solve so we could never really help you.  All you have discussed is your "solution" which you couldn't get to work and in my opinion is not a solution at all so I hope you are taking a different path.
wsh2Commented:
@PatHartman
Did you try the code? In the 5' example.. even though it is escaped out as '5''' (which you have to do for it to work).. the end result in the database is 5' (5 feet) which is exactly what the data stored is supposed to be.

Escaping is NOT changing data. Escaping is used to comply with SQL syntactical rules. It is so the SQL interpreter knows that the intent of the ' it is encountering is that it is data and NOT a data delimiter

I encourage you to give the code a try and then report back as to what is stored in the database. You will find that the data in the database reads ...

    5'

... just as it should.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.