Update Statement not working if value contains a apostrophe (1 Viewer)

fernando.rosales

Registered User.
Local time
Today, 06:52
Joined
Jun 11, 2014
Messages
27
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

Registered User.
Local time
Today, 06:52
Joined
Jun 11, 2014
Messages
27
Issue was due to the Where condition. I only had where field is null but forgot to include, or field = ''.
 

Ranman256

Well-known member
Local time
Today, 09:52
Joined
Apr 9, 2015
Messages
4,339
That's why you use queries.
It won't get the syntax wrong.
 

Users who are viewing this thread

Top Bottom