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:
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))"
'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))"
I have tried a number of things to deal with the single quote issue, but have had no success