Apostrophes with INSERT statements

[Tim2525]

Hi All,

I'm doing an INSERT of data from a temp table to another table. My issue is one of my fields has data with an apostrophe in it and it's messing up my insert. Is there a way of working around this where the data is inserted into the field including the apostrophe. Below is my insert statement.

Example of data: Dr's A and B

strSQL = "INSERT INTO tblPhysicians (drLicense, firstName, middleName, lastName, suffix, " & _
"specialty, subSpecialty, practiceName, address, address2, city, state, " & _
"zipCode, phone, extension, fax, alternateAddress, alternateAddress2, " & _
"alternateCity, alternateState, alternateZipCode, alternatePhone, alternateExtension, alternateFax) " & _
"VALUES ('" & Trim(strLicNbr) & "', '" & Trim(strFirst) & "', " & _
"'" & Trim(strMiddle) & "', '" & Trim(strLast) & "', '" & Trim(strSuffix) & "', " & _
"'" & Trim(strSpecialty) & "', '" & Trim(strSubSpecialty) & "', '" & Trim(strGroup) & "', " & _
"'" & Trim(strAddress) & "', '" & Trim(strAddress2) & "', '" & Trim(strCity) & "', " & _
"'" & Trim(strState) & "', '" & Trim(strZip) & "', '" & Trim(strPhone) & "', " & _
"'" & Trim(strExt) & "', '" & Trim(strFax) & "', '" & Trim(strAltAddress) & "', " & _
"'" & Trim(strAltAddress2) & "', '" & Trim(strAltCity) & "', '" & Trim(strAltState) & "', " & _
"'" & Trim(strAltZip) & "', '" & Trim(strAltPhone) & "', '" & Trim(strAltExt) & "', " & _
"'" & Trim(strAltFax) & "');"

TIA,

Tim

 

[RoyVidar]

For each of the criteria which can contain apostrophes, try something like this

[tt]...'" & replace(Trim(strAltFax), "'", "''") & "');"[/tt]

which will double up the number of apostrophes in the string, which will make the db accept it as one - strange but ...

Roy-Vidar

 

[Tim2525]

Thanks RoyVidar. Will give it a try.