troubleshooting Question

VBA Problem replacing apostrophes

Avatar of Rick Rudolph
Rick RudolphFlag for United States of America asked on
VBAMicrosoft Access
15 Comments4 Solutions2477 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 4 Answers and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros