asked on

Syntax issue with not in list code

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?

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Prew

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 Lambert

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