Solved

update query variable with apostrope

Posted on 2016-10-16
7
31 Views
Last Modified: 2016-10-17
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
0
Comment
Question by:fordraiders
7 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 41846136
Change this line
 "[csg_email_address] = '" & empEM & "' " &
To
 "[csg_email_address] = '" & replace(empEM,"'","''") & "' " &
0
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 41846140
pls apply same logic as what was proposed by Haris for all char fields in your update query.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41846143
still getting error.

'james.O"KEEFE@XXXXX.COM'

Thnx
fordraiders
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 75
ID: 41846149
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
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 41846150
OR

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),"") & "*' "
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 41846170
thanks joe, worked the best !
0
 
LVL 75
ID: 41846924
You are welcome.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question