Single Quote in value causes error

  • Thread starter Thread starter Eddy
  • Start date Start date
E

Eddy

This is the text of the sql line I am trying to execute

sql="UPDATE Leads SET Leads.Name = ' " & Forms!frmInvoice!
frmCustomer!CompanyName & " ' WHERE ID=5"

If I use a value without a single quote in the
CompanyName, it works fine. If I use something like
Larry's Steakhouse, it fails. Is there any way to get this
to work for all instances?

Thanks
 
sql="UPDATE Leads SET Leads.Name =" & chr(34) &
Forms!frmInvoice!frmCustomer!CompanyName & chr(34) & " WHERE ID=5"
 
sql="UPDATE Leads SET Leads.Name = ' " & Forms!frmInvoice!
frmCustomer!CompanyName & " ' WHERE ID=5"
Click to expand...

Use double quotes to delimit the string. You have to double them up
in the string itself. And I presume you don't actually want the extra
spaces either side of the name so you get

sql="UPDATE Leads SET Leads.Name = """ & Forms!frmInvoice!
frmCustomer!CompanyName & """ WHERE ID=5"
 
Use double quotes to delimit the string. You have to double them up
in the string itself.
Click to expand...

The second part is important, because otherwise you'll have problems with

Larry's "Olde" Hamburger Joint

The simplest thing is to write a short vba function that will take cear of
it all:

strSQL = strSQL & "WHERE FullName = " & SQLString(txtEnterName)

....


' bear in mind this has not been tested
private function SQLString (SomeText as Variant) as String

if isnull(sometext) then
' no quotes; return the actual null value
SQLString = "NULL"

else
' quote it externally and internally
SQLString = Chr$(34) & _
Replace(SomeText, Chr$(34), Chr$(34) & Chr$(34)) & _
Chr$(34)

end if
end function


There is a similar one for SQLDate, and a trivial one for SQLNumber.

Hope that helps


Tim F
 
Back
Top