Problem with " and ' in text field

[CD Tom]

In my data base I have user names with both " and ' in the name example "Doc" Holand or maybe Al Fiddlin' if the variable is vname and when inserting this into the database I just use the """ & vname & """ it excepts the Al Fiddlin' just fine but have a problem with the "Doc" Holand is the a answer to this problem? If so I would sure appreciate what it is.
Thanks again for all your help

[NeoPa]

My first suggestion would be to ensure you stick with a single type of quote when using SQL strings. My preference, and the actual standard, is to use the single-quote ('). See Quotes (') and Double-Quotes (") - Where and When to use them.

Once you have a quote type in use then you can ignore problems with the other type. '"Doc" Holland' is handled perfectly well without any complications.

Values that include the same type of quote can simply be doubled up. This should probably be standard practice in all your code :

strVal = Replace("O'Dwyer","'","''")
strSQL = Replace("SELECT * FROM [Table] WHERE ([NameField]='%V')", "%V", strVal)

Whenever either VBA or SQL come across the next occurrence of the quote character used to initiate a string value, they do a read-ahead for the next character. If it's the same character then they treat it as a single character value and continue parsing the string. If it's any other character they treat it as the end of the string.

[CD Tom]

Ok, none of the names have both the " and '. I would like to change the " to a ' as most of the name already have the single quote, like the Al Feddlin' I find that this is easy to use in a sql statement by just using """ & VName & """ and that works just fine. I've tried a couple of ways to change the " to a single ' but haven't found the answer yet. Thanks

[CD Tom]

I found the answer to removing the double quote from the name field. By using the Chr(34) in place of the " it works just like I wanted it to. Thanks for the suggestion about the replace statement I've never used that before and it will come in handy I'm sure.

[NeoPa]

I find both of these posts very difficult to understand. I'm not sure I do fully.

The question was about handling quotes in strings as passed to SQL. Removing or replacing them (except in as far as replacing them with a string that resolves eventually to them anyway) is something completely different from the original question.

You don't say at what stage you use Chr(34) in your code in order to get this to work as required. Whatever the case, there is no situation where using Chr(34) has any different effect than using the " character itself expressed correctly in VBA as "" within any literal string. As explained in the previously linked article any quote character, when used within a string delimited by the same character, need only be doubled up to represent the single copy of that character.

Many people use the following in their code :

X = Chr(34) & "My text" & Chr(34)

This is simply because they haven't realised that it is just a more, and unnecessarily, complicated version of :

X = """My text"""

[CD Tom]

I know I'm not to clear but let my try and explain. I get master data (names, address etc) from an other database I have not control over, I just started getting this so haven't had the problem before. This is where the names with the double quotes come from. My program has never had to deal with the double quote in a name field, I do have single quotes in the name like O'Donald the names are put in a variable that is later inserted into different fields in different tables. If I try and insert them using the standard string variable. ' " & VName & " ' it gives me an error because of the single quote being the delimiter. I have found that if I insert them using """ & VName & """ it works fine. Now that I'm getting the double quote in the name field this has been working. I couldn't get the double quote to work no matter what combination of " " I used. So I tried using the ASCI character chr(34) and that worked replacing the double quote with a single quote to match what is already in the name field.

VName = replace(VName, Chr(34), "'")

I use this just after I get the name from the name field in the database so when ever it tries to insert that name into a table I don't get any errors.
I don't know if this makes any sense as it's hard to explain. Thanks for taking your time to help with this.

[zmbd]

so, to sum up...
Your original problem is that your database was designed to handle the single ( ' ) quote within one of the data fields. A new data source has been added to, or exchanged for, the current data source wherein the new data source has the double ( " ) quotes and this conflicts with how you've designed the SQL

To resolve this issue...
You are standardizing the type of quote used in the field to the ('). To do this, you are replacing all of the (") quotes in the new data with the (').

Hence...
Once this is done, then the original issue you had with the type of quote and how to escape it within the SQL is simplified to escaping only the ('), as originally designed, instead of having to test for which type, or if both types, of quote are present in the name field.

is that correct?

[CD Tom]

That's correct, it was easier to modify the new data to the current program than changing the program to accommodated the new data coming in.

[NeoPa]

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 straightforward ly 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.

[CD Tom]

Yes that is correct, each occurrence of double quote is changed to a single quote. The funny thing that when I was trying to use your example of

VName = Replace(VName, """", "'")

I was using """"" and kept getting an error with only using """" it worked fine. My question is seeing you surround your find character with a double quote where is the double quote in the """" find. I'm probably not going to change every occurance of the double quote as it it working with the Chr(34). I've learned lots from this and want to thank everybody for their help.

[NeoPa]

I hope I'm clarifying here the question you're actually asking :
"""" is a sequence of four x double-quote (") characters.

This is found in VBA code so, as anywhere in VBA code, this would be treated as a delimited string. The first and last (fourth) characters are therefore string delimiters and do not occur within the literal string itself. This leaves the second and third characters as the contents of the string literal.

As we know, when a character that matches the delimiter character, as both the remaining characters do, is found anywhere within a string then it is first determined to be either a single or double instance. In this case we know it's a double. As such, this is treated by VBA as a single occurrence within the string value as opposed to a character marking the end of the string itself - or delimiter if you will.

Thus, what we're left with from all those four characters is a string of a single character, to whit a double-quote. A literal string that had two double-quotes in it would be represented in VBA as six double-quotes ("""""").

NB. When used in SQL the same rules apply. This can be in spite of having been applied already within VBA. Be careful and understand these rules thoroughly and there's no need to go wrong. Using single-quotes (') for SQL makes this less of a worry and easier to deal with, but even using double-quotes it can all be handled with ordinary characters from the keyboard.