runtime error 3075 help 1

[davyre]

When I run the query, I got runtime error 3075 syntax error(missing operator) in query expression. Is there something wrong with the query below?

StrSQL1 = "INSERT INTO tmpPartsTableAll ([TmpPartNumber], [TmpUnitID], [TmpPartDescription], [TmpMaterial], " & _
                      "[TmpQty], [TmpPartCost], [TmpVendorPart], [TmpVendorID], [TmpVendorDesc], [TmpComments])" & _
                      "VALUES ( " & StrPartNumber & "," & StrUnitID & ", " & StrPartDescription & ", " & StrMaterial & ", " & StrQty & ", " & StrPartCost & ", " & StrVendorPart & ", " & StrVendorID & ", " & StrVendorDesc & ", " & StrComments & ")"

docmd.setwarnings False
docmd.runSQL StrSQL1
docmd.setwarnings True

I have stared it for 30 mins and I still cant find what is wrong with it. The VALUES() should be in a line but in this thread it wont fit so I divide it into two lines. Any help will much appreciated. Thanks!

 

[PHV]

Use single quotes for text values, eg:

StrSQL1 = "INSERT INTO tmpPartsTableAll ([TmpPartNumber], [TmpUnitID], [TmpPartDescription], [TmpMaterial], " & _
                      "[TmpQty], [TmpPartCost], [TmpVendorPart], [TmpVendorID], [TmpVendorDesc], [TmpComments])" & _
                      "VALUES ([!]'[/!]" & StrPartNumber & "[!]'[/!]," & StrUnitID & ",[!]'[/!]" & StrPartDescription & "[!]'[/!]," & _
                      StrMaterial & "," & StrQty & "," & StrPartCost & ",[!]'[/!]" & StrVendorPart & "[!]'[/!]," & _
                      StrVendorID & ",[!]'[/!]" & StrVendorDesc & "[!]'[/!],[!]'[/!]" & StrComments & "[!]'[/!])"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

Thanks PHV, it helped me with the first and second entry on the query. But when the loop do for the third time (aka inserting the third entry, it shows error 3075 again, particularly at StrPartDescription, StrMaterial, StrQty, StrPartCost, StrVendorPart, StrVendorID, StrVendorDesc. It was successful for the first and second iteration, but why at the third iteration it shows error again?
I have checked the StrPartDescription etc and the data type should be the same as before (the first and second). Do you have any idea why?Thanks

 

[davyre]

geez, I think I got the problem. In the description, the data contains single quote(') that messes up with the code. I got the problem solved. Thanks!

 

[PHV]

A safe way is to replace this;
[tt]",'" & StrVendorDesc & "','"[/tt]
with this:
[tt]",'" & Replace(StrVendorDesc, "'", "''") & "','"[/tt]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

thanks a lot!