Acc2K - SQL Fails due to Single Quote in Field

[MindBender77]

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.

strSQL = "INSERT Into tbl_shipping_label ([Our Street],[Our City],[Address Service]," & _
"[First Class],[Postage],[Our City2],[Permit],[Pt Name],[Pt Street],[Pt City],[Pt CC],[Pt Orderno])" & _
"values ('" & astrOrdernotes(1) & "','" & astrOrdernotes(2) & "','" & astrOrdernotes(3) & "'," & _
"'" & astrOrdernotes(4) & "','" & astrOrdernotes(5) & "','" & astrOrdernotes(6) & "','" & astrOrdernotes(7) & "'," & _
"'" & astrOrdernotes(8) & "','" & astrOrdernotes(9) & "','" & astrOrdernotes(10) & "','" & astrOrdernotes(11) & "'," & _
"'" & astrOrdernotes(12) & "');"
CurrentDb.Execute strSQL, dbFailOnError
 

Any suggestions would be appreciated,
Bender

[Stewart Ross]

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:

strSQL = "INSERT Into tbl_shipping_label ([Our Street],[Our City],[Address Service]," & _
"[First Class],[Postage],[Our City2],[Permit],[Pt Name],[Pt Street],[Pt City],[Pt CC],[Pt Orderno])" & _
"values (@" & astrOrdernotes(1) & "@,@" & astrOrdernotes(2) & "@,@" & astrOrdernotes(3) & "@," & _
"@" & astrOrdernotes(4) & "@,@" & astrOrdernotes(5) & "@,@" & astrOrdernotes(6) & "@,@" & astrOrdernotes(7) & "@," & _
"@" & astrOrdernotes(8) & "@,@" & astrOrdernotes(9) & "@,@" & astrOrdernotes(10) & "@,@" & astrOrdernotes(11) & "@," & _
"@" & astrOrdernotes(12) & "@);"
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

[MindBender77]

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

[NeoPa]

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 ('') :

..."','" & 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.