"Missing Operator"

K

kidkosmo

Hey Gang,

I am running the code below to import data from another database.
Even though it's messy, it works fairly well; however, I am now
getting an occasional "Missing operator" error. I've looked at the
data that I'm pulling in for the rst and the error is caused when the
actual data has an apostrophe in it (i.e. St. Ann's). Because I have
the string separated by apostrophes, it's throwing everything off. Is
there a better way for me to write this SQL to capture literal values
and not mess up the number of delimiters with that darn apostrophe??

Thanks!


sP = "', '"

Call Build_ProductTypeSQL
'Debug.Print strSQL

Set wsJet = DBEngine(0)
'Debug.Print "Jet Database "; wsJet.Databases.Count & "-"
& CurrentDb.Name

Set dbJet = wsJet.OpenDatabase("C:\Documents and Settings
\xxxxxxx.mdb")
'Debug.Print "Jet Database "; wsJet.Databases.Count &
"-" & dbJet.Name


Set rst = dbJet.OpenRecordset(ProductTypeSQL)
DoCmd.SetWarnings False


If Not rst.BOF Then rst.MoveFirst

Do Until rst.EOF

Debug.Print rst!Contact_name

AppendSQL = "INSERT INTO tblTEST (NUMBERPRGN, CATEGORY, ESTOpenDate,
PRODUCT_TYPE, " & _
"OPENED_BY, PRIORITY_CODE, USER_PRIORITY, UPDATE_TIME, ASSIGNMENT,
STATUS, CLOSE_TIME, CLOSED_BY, VENDOR, " & _
"CAUSE_CODE, RESOLUTION_CODE, ASSIGNEE_NAME, CONTACT_NAME " & _
") VALUES (" & _
rst!NUMBERPRGN & sP & rst!Category & sP & DateAdd("h",
-5, rst!Open_Time) & sP & _
rst!PRODUCT_TYPE & sP & rst!OPENED_BY & sP & IIf(IsNull
(rst!PRIORITY_CODE), 3, rst!PRIORITY_CODE) & sP & _
IIf(IsNull(rst!USER_PRIORITY), 3, rst!USER_PRIORITY) &
sP & rst!UPDATE_TIME & sP & rst!ASSIGNMENT & _
sP & rst!Status & sP & rst!CLOSE_TIME & sP & rst!
CLOSED_BY & sP & rst!VENDOR & sP & rst!CAUSE_CODE & sP & _
rst!RESOLUTION_CODE & sP & rst!ASSIGNEE_NAME & sP &
rst!Contact_name
'Debug.Print AppendSQL

DoCmd.RunSQL AppendSQL


rst.MoveNext

Loop
 
P

Piet Linden

Hey Gang,

I am running the code below to import data from another database.
Even though it's messy, it works fairly well; however, I am now
getting an occasional "Missing operator" error.  I've looked at the
data that I'm pulling in for the rst and the error is caused when the
actual data has an apostrophe in it (i.e. St. Ann's).  Because I have
the string separated by apostrophes, it's throwing everything off.  Is
there a better way for me to write this SQL to capture literal values
and not mess up the number of delimiters with that darn apostrophe??

Thanks!

        sP = "', '"

            Call Build_ProductTypeSQL
                      'Debug.Print strSQL

            Set wsJet = DBEngine(0)
             'Debug.Print "Jet Database "; wsJet.Databases.Count & "-"
& CurrentDb.Name

            Set dbJet = wsJet.OpenDatabase("C:\Documents and Settings
\xxxxxxx.mdb")
                'Debug.Print "Jet Database "; wsJet.Databases.Count &
"-" & dbJet.Name

                Set rst = dbJet.OpenRecordset(ProductTypeSQL)
                        DoCmd.SetWarnings False

                    If Not rst.BOF Then rst.MoveFirst

                    Do Until rst.EOF

                    Debug.Print rst!Contact_name

AppendSQL = "INSERT INTO tblTEST (NUMBERPRGN, CATEGORY, ESTOpenDate,
PRODUCT_TYPE, " & _
    "OPENED_BY, PRIORITY_CODE, USER_PRIORITY, UPDATE_TIME, ASSIGNMENT,
STATUS, CLOSE_TIME, CLOSED_BY, VENDOR, " & _
    "CAUSE_CODE, RESOLUTION_CODE, ASSIGNEE_NAME, CONTACT_NAME " & _
        ") VALUES (" & _
                rst!NUMBERPRGN & sP & rst!Category & sP &DateAdd("h",
-5, rst!Open_Time) & sP & _
                rst!PRODUCT_TYPE & sP & rst!OPENED_BY & sP & IIf(IsNull
(rst!PRIORITY_CODE), 3, rst!PRIORITY_CODE) & sP & _
                IIf(IsNull(rst!USER_PRIORITY), 3, rst!USER_PRIORITY) &
sP & rst!UPDATE_TIME & sP & rst!ASSIGNMENT & _
                sP & rst!Status & sP & rst!CLOSE_TIME & sP & rst!
CLOSED_BY & sP & rst!VENDOR & sP & rst!CAUSE_CODE & sP & _
                rst!RESOLUTION_CODE & sP & rst!ASSIGNEE_NAME & sP &
rst!Contact_name
    'Debug.Print AppendSQL

                            DoCmd.RunSQL AppendSQL

                    rst.MoveNext

                    Loop
Click to expand...

Two ways to do it. The easier way is to double the quotes in the
literal values you are trying to append before running the SQL
statement. (Because the single apostrophe is being interpreted as a
text delimiter instead of a literal value) You can use REPLACE for
this...

?Replace("Expression","find string","replace
string",lngStartPosition,lngCount,vbTextCompare)

Maybe the easiest way to implement this is to create intermediate
string variables and then use the string variables instead of the
literal values in your SQL string.

e.g.
strSomeValue = Replace(strSomeValue," ' "," ' ' ")

(but remove all the spaces inside the quotes... they're there so you
can see the individual single quotes.)

Otherwise, you would have to use a recordset and set the individual
values for the fields in the recordset before updating
 
Top