Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.
Solved
VBA Problem replacing apostrophes
Posted on 2016-07-15
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.SubContractor
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.SubContract
DoCmd.RunSQL (DWstrSQL)
End Function
I have tried a number of things to deal with the single quote issue, but have had no success
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.SubContractor
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.SubContract
DoCmd.RunSQL (DWstrSQL)
End Function
I have tried a number of things to deal with the single quote issue, but have had no success
4-
4
-
4
- +2
15 Comments
Active today
I'm not sure why you want to replace them with carets - just double them -
When using vba,
When using vba,
Replace(Data, "'", "''")
Active today
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.
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.SubContract
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.SubContract
The debug string on this looks like:
UPDATE PRTrans_Export SET PRTrans_Export.SubContract
So I think that we are close, just not quite there.
Any more suggestions?
DWstrSQL = "UPDATE PRTrans_Export SET PRTrans_Export.SubContract
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.SubContract
The debug string on this looks like:
UPDATE PRTrans_Export SET PRTrans_Export.SubContract
So I think that we are close, just not quite there.
Any more suggestions?
Active today
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.SubContract
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!
If you must, then try this syntax to convert back:
DWstrSQL = "UPDATE PRTrans_Export SET PRTrans_Export.SubContract
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!
To follow up on this. I have tried the suggestions mentioned.
This string works in a query windows:
UPDATE PRTrans_Work SET PRTrans_Work.Subcontractor
The ^ is replaced by a '
This SQL statement does not work in VBA code
DWstrSQL = "UPDATE PRTrans_Export SET PRTrans_Export.SubContract
Debug.Print DWstrSQL
DoCmd.RunSQL (DWstrSQL)
Even though the Debug.Print displays the following:
UPDATE PRTrans_Export SET PRTrans_Export.SubContract
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?
This string works in a query windows:
UPDATE PRTrans_Work SET PRTrans_Work.Subcontractor
The ^ is replaced by a '
This SQL statement does not work in VBA code
DWstrSQL = "UPDATE PRTrans_Export SET PRTrans_Export.SubContract
Debug.Print DWstrSQL
DoCmd.RunSQL (DWstrSQL)
Even though the Debug.Print displays the following:
UPDATE PRTrans_Export SET PRTrans_Export.SubContract
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?
Active today
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.
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.
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], "'", "''") & "'"
Active today
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.
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.
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.
@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.
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.
Active today
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"'
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"'
Please allow me to simplify my VBA code from above and clarify it by replacing all apostorphes (single quote) with Chr(39):
--------------------------
Using your example of 5' (5 feet), and the above code, the finished SQL statement will read:
Which is what I believe you (@PatHartman) presented as well.
And in this, I do believe we both agree.. {smile}
'
' 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)
--------------------------
Using your example of 5' (5 feet), and the above code, the finished SQL statement will read:
UPDATE [MyTable]
SET [MyTable].[MyLength] = '5'''
Which is what I believe you (@PatHartman) presented as well.
And in this, I do believe we both agree.. {smile}
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.
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.
Active today
@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.
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.
@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.
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.
Featured Post
Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This article presents several of my favorite code snippets.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With just a little bit of SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form. If you have never written code or looked at an SQL statement before, no problem! ... give i…
Suggested Courses
Course of the Month10 days, 16 hours left to enroll
607 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.