Avatar of Rick Rudolph
Rick Rudolph
Flag for United States of America asked on

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
VBAMicrosoft Access

Avatar of undefined
Last Comment
wsh2

8/22/2022 - Mon
Lee W, MVP

I'm not sure why you want to replace them with carets - just double them -

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

Open in new window

SOLUTION
Lee W, MVP

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rick Rudolph

ASKER
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?
SOLUTION
Scott McDaniel (EE MVE )

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rick Rudolph

ASKER
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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
PatHartman

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.
SOLUTION
wsh2

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rick Rudolph

ASKER
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.
wsh2

@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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PatHartman

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"'
wsh2

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 Rudolph

ASKER
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
PatHartman

@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.
wsh2

@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.