Runtime error 3075 without operator
When I use ms access 2007 it works fine. I swtich to ms access 2010 and now it doesn't work.
DoCmd.RunSQL ("INSERT INTO Pending_Orders (Customer, ItemNumber, Description, Qty, [Order #], Temp, ShipDate) VALUES (" & _
"'" & Replace(rst!Customer, "'", "''") & "','" & rst![Item #] & "','" & rst!Description & "'," & rst!Qty & ",'" & rst![Order #] & "'," & NextTemp & ",#" & rst![Ship Date] & "#)")
I am getting this error now.
What could it be?
+3
source to share
2 answers
I would suggest that you replace single quotes in the above code with double quotes, this is one common problem with names like Paul O'Connor. Also use CurrentDb.Execute over DoCmd.RunSQL. By using currentBD, you can suppress annoying messages and see a lot more information. Finally, always use String to get the SQL query, so debugging can be much easier.
Try the following:
strSQL = "INSERT INTO Pending_Orders (Customer, ItemNumber, Description, Qty, [Order #], " & _
"Temp, ShipDate) VALUES (" & Chr(34) & rst!Customer & Chr(34) & _
", " & Chr(34) & rst![Item #] & Chr(34) & _
", " & Chr(34) & rst!Description & Chr(34) & _
", " & rst![Qty] & ", " & Chr(34) & rst![Order #] & Chr(34) & _
", " & NextTemp & ", " & Format(rst![Ship Date], "\#mm\/dd\/yyyy\#") & ")
CurrentDB.Execute strSQL
0
source to share
In addition to Paul Francis' answer, I propose the EscapeDBstring function, which solves the inline quote problem in "Paul O'Connor" by avoiding the inline quote:
' dbEscapeString -- escape the single quote in a string that will be included in
' an SQL statement. The single quote is the database/SQL string delimiter.
Public Function dbEscapeString(ByVal s)
Dim i
i = 1
While (i <= Len(s))
If (Mid(s, i, 1) = "'") Then
'If (Mid(s, i + 1, 1) = "'") Then ' this would consider two single quotes to be escaped already
' i = i + 1
'Else
s = Mid(s, 1, i) + Mid(s, i)
i = i + 1
'End If
End If
i = i + 1
Wend
dbEscapeString = s
End Function
0
source to share