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

Microsoft Access

Last Comment

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

8/22/2022 - Mon

Haris Dulic

10/16/2016

Change this line
"[csg_email_address] = '" & empEM & "' " &
To
"[csg_email_address] = '" & replace(empEM,"'","''") & "' " &

Ryan Chong

10/16/2016

pls apply same logic as what was proposed by Haris for all char fields in your update query.

Fordraiders

10/16/2016

ASKER

still getting error.

'james.O"KEEFE@XXXXX.COM'

Thnx
fordraiders

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

10/16/2016

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

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

10/16/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Fordraiders