Runtime Error 3075

[Purdue2479]

I am getting a runtime error 3075, "Syntax error (missing operator) in query expression.", when the sql code below trys to execute. The '" & msgerror & "' piece is causing the issue and I don't understand why. I have declared msgerror as a String to store the error description. Any ideas?

This is the msgerror value "Field 'Start Date Month Disp' doesn't exist in destination table 'tbl_temp_Import_test.'"

Does it not like the single and double quotes within the string?

If Err.number = 2391 Then

msgerror = Err.Description
  'intresponse = MsgBox("Import Failed on " & ShortFn & " , click Ok to ignore file or Cancel to quit import",vbOKCancel)
   DoCmd.RunSQL "INSERT INTO tbl_Failed_Import_Files ([Failed Files], [Field Name Causing Failure]) " & _
            "Values('" & ShortFn & "','" & msgerror & "');"
  Debug.Print ShortFn

 

[Purdue2479]

I figured out it doesn't like the single quotes by using Replace() to remove the single quotes before it's used in the sql statement. Is there any way around that besides removing the single quotes?

 

[tehNellie]

Quotes in SQL strings drive me nuts. When I want to include a variable in a SQL String I tend to use CHR(34), the ascii code for ". it's possibly just me but I find it a lot more readable than the plethora of quotes, double quotes, triple and sometimes quadruple quotes you can end up with otherwise:

strsql = "SELECT * FROM tblName WHERE column = " & _
           & chr(34) & strVariable & chr(34) & ";"

 

[supercool]

chr(34), em..... a new method, would try later