Solved

VBA Problem replacing apostrophes

Posted on 2016-07-15
15
103 Views
Last Modified: 2016-07-22
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
0
Comment
Question by:rrudolph
  • 4
  • 4
  • 4
  • +2
15 Comments
 
LVL 95

Expert Comment

by:Lee W, MVP
ID: 41713559
I'm not sure why you want to replace them with carets - just double them -

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

Open in new window

0
 
LVL 95

Assisted Solution

by:Lee W, MVP
Lee W, MVP earned 125 total points
ID: 41713560
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.
0
 

Author Comment

by:rrudolph
ID: 41714477
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?
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 41714500
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!
0
 

Author Comment

by:rrudolph
ID: 41714636
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?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41719655
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.
0
 
LVL 14

Assisted Solution

by:wsh2
wsh2 earned 125 total points
ID: 41723650
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

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 34

Accepted Solution

by:
PatHartman earned 125 total points
ID: 41723661
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.
0
 

Author Comment

by:rrudolph
ID: 41723665
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.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 41723682
@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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41723695
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"'
0
 
LVL 14

Expert Comment

by:wsh2
ID: 41723771
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}
0
 

Author Comment

by:rrudolph
ID: 41723842
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41724766
@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.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 41724850
@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.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now