MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Double Quotation on String Value

  Asked By: Seth    Date: Sep 29    Category: MS Office    Views: 1556

I have this code:

cr = Chr(34) & ws.Cells(x, 2).Value & Chr(34)
sSQL = "Insert into db Values(" & cr & ")"
rst.Open sSQL, sConn

obviously, it's for a database, and Access Database actually. I am
uploading the data from an excel workbook to the access database.

But I keeping getting an error (Error -214217900: Syntax Error (Missing
Operator) in query expression) whenever the value of ws.cells(x,2) has
a double quotation on it (") For example, the value is:

February 2008 - "Inventory"

What should I do to not get this error? I don't want to remove all the
those double quotations on every cell.



5 Answers Found

Answer #1    Answered By: Jawna Mohammad     Answered On: Sep 29

I haven't used SQL in VBA for Access in a long time (++years)
I use SQL in VBA for Oracle daily.
To insert  the value into a varchar field, shouldn't the string  be delimited?

So the resulting string would be:

Insert into db Values('February 2008 -"Inventory"')

(note the single quotes as delimiters)
So, your VBA code  would be:

sSQL = "Insert into db Values('" & cr & "')"

I'm not 100% positive about the MSAccess applicability.
Especially, since in Oracle, I would have to specify the field names in the db.

sSQL = "Insert into db (datefield) Values('" & cr & "')"

Answer #2    Answered By: Venkat Rulez     Answered On: Sep 29

Thanks for the reply sir. But the problem with single quotes
delimiters, is the same as double  quotes. If there would be a single
quote on my string  like say:

February '08

then it would be the same error.

Answer #3    Answered By: Minal Nayak     Answered On: Sep 29

As you're aware, it is the double  quotes in your cells that are interfering
with your SQL. There are two ways to address this:

1) If you have double quotes, but never have single quotes in your cells,
then use single quotes as your delimiters rather than double quotes. I.e.

> cr = "'" & ws.Cells(x, 2).Value & "'"

2) But if you have both kinds, then you'll need to convert your quotes to
pairs of quotes.

Because it's easier to read, I always tend to use single quotes as the
delimiters, but you can do it with double quotes if you prefer. This little

Option Explicit

Private Sub CommandButton1_Click()
Dim WS As Worksheet: Set WS = ActiveSheet
Dim X As Integer
For X = 1 To 3
Dim CR As String
CR = """" & Replace(WS.Cells(X, 2).Value, """", """""") & """"
Cells(X, "D").Value = CR
CR = "'" & Replace(WS.Cells(X, 2).Value, "'", "''") & "'"
Cells(X, "E").Value = CR
Next X
End Sub

puts a double-quote delimited version in D and a single-quote delimited
version in E.

Starting with February '2008' - "Inventory"

I get "February '2008' - ""Inventory""" for doubles and 'February ''2008'' -
"Inventory"' for singles. (Note that these are hard to read in a mail
browser, and are best copied to a mono-space text editor for inspection.

Answer #4    Answered By: Haru Tanaka     Answered On: Sep 29

Yes, I have both kinds.

So, that's the only way, replacing the double  quotes? Aren't there
any other delimiters? Just single and double quotes?

I just hope that this won't take too long to run it.

Answer #5    Answered By: Jacob Evans     Answered On: Sep 29

The way to put quotes inside quotes in SQL is to double  them. I'm not aware
of any alternatives.

This is an SQL requirement, not an Excel limitation.

Didn't find what you were looking for? Find more on Double Quotation on String Value Or get search suggestion and latest updates.