By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,660 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,660 IT Pros & Developers. It's quick & easy.

Acc2K - SQL Fails due to Single Quote in Field

MindBender77
100+
P: 234
Hello All,
The following SQL string fails because astrOrdernotes(8) has a single quote in the field. Example Henry O'Smith. The data type for this field is a string but, I keep getting a syntax error starting with this field.

Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT Into tbl_shipping_label ([Our Street],[Our City],[Address Service]," & _
  2. "[First Class],[Postage],[Our City2],[Permit],[Pt Name],[Pt Street],[Pt City],[Pt CC],[Pt Orderno])" & _
  3. "values ('" & astrOrdernotes(1) & "','" & astrOrdernotes(2) & "','" & astrOrdernotes(3) & "'," & _
  4. "'" & astrOrdernotes(4) & "','" & astrOrdernotes(5) & "','" & astrOrdernotes(6) & "','" & astrOrdernotes(7) & "'," & _
  5. "'" & astrOrdernotes(8) & "','" & astrOrdernotes(9) & "','" & astrOrdernotes(10) & "','" & astrOrdernotes(11) & "'," & _
  6. "'" & astrOrdernotes(12) & "');"
  7. CurrentDb.Execute strSQL, dbFailOnError
  8.  
Any suggestions would be appreciated,
Bender
Dec 17 '08 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
Hiya Bender. Problem is that you are using single quotes as string delimiters for the VALUES part of your string, and when a single quote is encountered within the value it ends the string too early (as in 'O'Smith').

Access will allow the use of double-quotes in the SQL string, but you cannot type these in directly without them getting really messy in appearance (from the repeats that are necessary). The example below uses an approach of substituting another uncommon character for the double quotes in building the string (an @ in this case), then replacing these with double quotes after building:

Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT Into tbl_shipping_label ([Our Street],[Our City],[Address Service]," & _
  2. "[First Class],[Postage],[Our City2],[Permit],[Pt Name],[Pt Street],[Pt City],[Pt CC],[Pt Orderno])" & _
  3. "values (@" & astrOrdernotes(1) & "@,@" & astrOrdernotes(2) & "@,@" & astrOrdernotes(3) & "@," & _
  4. "@" & astrOrdernotes(4) & "@,@" & astrOrdernotes(5) & "@,@" & astrOrdernotes(6) & "@,@" & astrOrdernotes(7) & "@," & _
  5. "@" & astrOrdernotes(8) & "@,@" & astrOrdernotes(9) & "@,@" & astrOrdernotes(10) & "@,@" & astrOrdernotes(11) & "@," & _
  6. "@" & astrOrdernotes(12) & "@);"
  7. strSQL = Replace(strSQL, "@", Chr(34))
In testing this I substituted for the array references to make sure it worked as expected, and certainly the string built was valid.

I could not execute the statement you supplied, but I did test the approach on a simple table with string values containing single quotes and it worked as expected.

-Stewart
Dec 17 '08 #2

MindBender77
100+
P: 234
Thank you very much, Stewart, this problem is solved.

The most valuable information you've given was the explanation of why this problem was occurring. I modified the delimiter here and then later on in the module for the same issue. It now works flawlessly......so far.

Good day to you and again, Thank You,

Bender
Dec 17 '08 #3

NeoPa
Expert Mod 15k+
P: 31,487
See Quotes (') and Double-Quotes (") - Where and When to use them.

My advice (normally very much in line with Stewart's but diverges somewhat here) would be to apply a quote doubler to the data. A wrapper function that replaces single-quotes (') with two single-quotes ('') :
Expand|Select|Wrap|Line Numbers
  1. ..."','" & DWrapper(astrOrderNotes(9)) & "','" & ...
There are other ways of handling the issue, but handling the string terminators in SQL in a non-SQL-standard way may cause problems if you ever try to move out of the Access arena.

That may be entirely trivial in your circumstances, so you judge whether this is worth worrying about or not. It certainly works in Access.
Dec 23 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.