This may take a little more unravelling than I expected. I believe now, since ZMBD's helpful and clarifying post, we have a much fuller understanding of your situation. Just confirm one thing for me though, if you would :
You are changing each occurrence in your data of double-quotes to be single quotes?
That is to say that if data comes in such as
"Doc" Holland, you'd be changing that to be
'Doc' Holland. Certainly that's what seems to me to be what you're doing.
Let me say that this is unnecessary. I'll explain further.
I'm going to assume here that you have already decided to use the double-quote (") to delimit text strings in your SQL code. As you know, I recommend to use the single-quote (') for reasons explained in my earlier post, but it seems you've already gone for the double so I'll continue on that basis for your benefit.
As you know, when dealing with single-quotes within your data when the delimiter you're using is the double-quote, there are basically no issues. This works straightforwardly as expected.
This leaves us with the double-quotes. EG.
"Doc" Holland. In all my explanations below I'll assume that the original value of
VName is
"Doc" Holland. We're also working on the basis that you want to use this value in some SQL string later on. My guess is a command similar to :
- strSQL = "SELECT * FROM [YourTable] WHERE ([VName]=""" & VName & """)"
As a side-note, this is more easily accomplished using the single-quote delimiter, as shown here, but we'll continue on the basis of using the double :
- strSQL = "SELECT * FROM [YourTable] WHERE ([VName]='" & VName & "')"
Currently you're using :
- VName = Replace(VName, Chr(34), "'")
From my earlier comment about doubling quotes up when used within strings delimited by the same type of quote, we see this is
exactly equivalent to :
- VName = Replace(VName, """", "'")
In both cases the resultant value in
VName is
'Doc' Holland.
However, if we wanted the eventual value in the SQL string to show
"Doc" Holland then we could approach it differently (This is what I was trying to get across before). We could replace all instances of the double-quote character with two instances of it (IE.
"Doc" Holland becomes
""Doc"" Holland). This can be awkward to read as it involves doubling up twice :
- VName = Replace(VName, """", """""")
In this case the resultant value in
VName is
""Doc"" Holland.
Going back to the code that creates the SQL string we get a value of :
- SELECT * FROM [YourTable] WHERE ([VName]="""Doc"" Holland")
This exactly matches the original data wherever found in your table.