Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-06-2017, 07:00 AM   #1
fernando.rosales
Newly Registered User
 
Join Date: Jun 2014
Posts: 27
Thanks: 5
Thanked 2 Times in 2 Posts
fernando.rosales is on a distinguished road
Update Statement not working if value contains a apostrophe

Hi everyone I have a statement which works fine if the value of the cell does not contain any special characters. The problem is if the value contains a ' symbol than it does not work. How can I work around this?

Code:
 DoCmd.RunSQL "UPDATE tbl_Audit_summary INNER JOIN tbl_area_list_tmp ON (tbl_Audit_summary.audit_area = tbl_area_list_tmp.area) SET tbl_Audit_summary.business = [tbl_area_list_tmp].[business] where tbl_Audit_summary.business is null"
Example:

tbl_audit_summary table
Audit Area = DD's Area
Business = null

tbl_area_list
Area = DD's Area
Business = dd's

End result:
tbl_audit_summary table
Audit Area = DD's Area
Business = null -- Not working, should be dd's after the statement

fernando.rosales is offline   Reply With Quote
Old 01-06-2017, 07:04 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,320
Thanks: 0
Thanked 728 Times in 713 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Update Statement not working if value contains a apostrophe

Run it as a query ,not SQL.
Ranman256 is offline   Reply With Quote
Old 01-06-2017, 07:11 AM   #3
fernando.rosales
Newly Registered User
 
Join Date: Jun 2014
Posts: 27
Thanks: 5
Thanked 2 Times in 2 Posts
fernando.rosales is on a distinguished road
Re: Update Statement not working if value contains a apostrophe

Issue was due to the Where condition. I only had where field is null but forgot to include, or field = ''.

fernando.rosales is offline   Reply With Quote
Old 01-06-2017, 08:31 AM   #4
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,320
Thanks: 0
Thanked 728 Times in 713 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Update Statement not working if value contains a apostrophe

That's why you use queries.
It won't get the syntax wrong.

Ranman256 is offline   Reply With Quote
Reply

Tags
statement , update , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Update statement for a OUTER JOIN Select Statement. aniyahudi Queries 2 02-12-2014 01:47 PM
Update Statement is not working through VBA ria.arora Modules & VBA 10 07-03-2012 03:29 AM
Update statement not working as desired. aman Modules & VBA 2 02-09-2011 03:51 AM
WHERE portion of Update statement not working proballin Modules & VBA 3 03-10-2008 10:16 AM
Apostrophe in SQL Statement VBStudent Queries 1 04-03-2002 07:12 AM




All times are GMT -8. The time now is 09:42 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World