Syntax issue with not in list code

SteveL13
SteveL13 used Ask the Experts™
on
I'm having an issues when I try to add a string of characters that includes an apostrophe.  Here is the string...
SAM's (Somewhere, IL 61108 - UPS Ground)

It works fine without the apostrophe.

and here is the not in list code:

strSQL = "INSERT INTO tbSamplesTypesLU ([Description],[CustomerID]) VALUES ('" & NewData & "'," & Me.Parent.cboCustomerID & ");"

Can someone spot my problem?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014
Commented:
If your data might contain an apostrophe char, you should do this:
strSQL = "INSERT INTO tbSamplesTypesLU ([Description],[CustomerID]) VALUES (""" & NewData & """," & Me.Parent.cboCustomerID & ");"

Open in new window

Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
For the fields that can contain an apostrophe, just do a replace when you add them to the query and replace a single quote with two single quotes, like:

Replace(dataVariable,"'","''")

Open in new window


»bp
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Nope, it won't work either, because your data can hold simple quotes, double quote or any mix of both.

You should use a parameterized query instead.
Dim sql As String
sql = vbNullString
sql = sql & "PARAMATERS paramDescription Text(255)," & vbcrlf
sql = sql & "    paramCustomerID Long;" & vbcrlf
sql = sql & "INSERT INTO tbSamplesTypesLU (" & vbcrlf
sql = sql & "    Description,"  & vbcrlf
sql = sql & "    CustomerID)"  &vbcrlf
sql = sql & "VALUES (" & vbcrlf
sql = sql & "    [paramDescription]," & vbcrlf
sql = sql & "    {paramCustomerID]);"

Dim db As DAO.Database
Set db = currentDb

Dim qd As DAO.QueryDef
Set qd = db.CreateQueryDef("tempQD", sql)

qd.parameters("paramDescription").value = NewData
qd.parameters("paramCustomerID").value = CLng(Me.Parent.cboCustomerID)
qd.Execute dbfailonError
qd.Close

db.queryDefs.delete(qd.name)

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial