Link to home
Create AccountLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

update query variable with apostrope

access 2010 vba  sql  update statement
I'm trying to run a sql update but one of my variables may contain a apostrope.


code...
R![csg_email_address] = empEM



my_sql = "Update Step1_ADD_Parent_to_SATA_P2 SET [am_arm_racf] = '" & Uid & "', " & _
         "[AM_ARM_FName] = '" & empFN & "', " & _
         "[AM_ARM_LNAME] = '" & empLN & "', " & _
         "[csg_email_address] = '" & empEM & "' " & _
         "where [0BPARTNER] like '" & PntnChild & "*' "

CurrentDb.Execute my_sql, dbFailOnError

Open in new window




This line may contain an apostrope     "empEM" =   JAMES O'KEEFE
my UPDATE statement dies on execute.

I need to handle the apostrope.



Thanks
Fordraiders
Avatar of Haris Dulic
Haris Dulic
Flag of Austria image

Change this line
 "[csg_email_address] = '" & empEM & "' " &
To
 "[csg_email_address] = '" & replace(empEM,"'","''") & "' " &
pls apply same logic as what was proposed by Haris for all char fields in your update query.
Avatar of Fordraiders

ASKER

still getting error.

'james.O"KEEFE@XXXXX.COM'

Thnx
fordraiders
my_sql = "Update Step1_ADD_Parent_to_SATA_P2 SET [am_arm_racf] = '" & Uid & "', " & _
         "[AM_ARM_FName] = '" &  Replace([empFN], Chr(39),"") & "', " & _
         "[AM_ARM_LNAME] = '" & Replace([empLN], Chr(39),"") & "', " & _
         "[csg_email_address] = '" & Replace([empEM], Chr(39),"") & "' " & _
         "where [0BPARTNER] like '" & Replace([PntnChild], Chr(39),"") & "*' "

CurrentDb.Execute my_sql, dbFailOnError
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
thanks joe, worked the best !