[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 528
  • Last Modified:

access vba update syntax error

I have the below update query in vba where I'm trying to update the contents of a field with HTML text. I need help in how to get this string of HTML contents into the field.

This is the update statement:
DoCmd.RunSQL "UPDATE branch SET branch_text = " & [strHTML] & " WHERE branch_id = " & Form_frmReport.cboChooseBranch.value

?strhtml
<P class=MsoNormal><B style="mso-bidi-font-weight: normal"><SPAN style="COLOR: red">This is a test of the scripting system<?xml:namespace prefix = o /><o:p></o:p></SPAN></B></P>ok

I'm getting a syntax error, it looks like i may need to escape the strhtml with quotes....?
0
Dalexan
Asked:
Dalexan
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Michael VasilevskySolutions ArchitectCommented:
Use Replace() to replace the double quotes
0
 
DalexanAuthor Commented:
Please show the syntax within the docmd
0
 
Michael VasilevskySolutions ArchitectCommented:
e.g.

DoCmd.RunSQL "UPDATE branch SET branch_text = " & Replace([strHTML], Chr(34), """") & " WHERE branch_id = " & Form_frmReport.cboChooseBranch.value
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
DalexanAuthor Commented:
I still get the same error,
0
 
Dale FyeCommented:
iTry:

strSQL = "UPDATE branch SET branch_text = """ _
            & Replace([strHTML], chr$(34), chr$(34) & chr$(34)) _
            & """ WHERE branch_id = " & Form_frmReport.cboChooseBranch.value
DoCmd.RunSQL

The string must be wrapped in quotes, which is the reason for the triple quotes before and after the Replace() function.  And in order for [strHTML] to be interpretted properly in strSQL, you must also replace the single quotes in that string with double quotes, which is the reason for the use of the Replace() function.
0
 
Dale FyeCommented:
oops, that should be:

docmd.runsql strsql

or better yet

currentdb.execute strsql, dbfailonerror
0
 
Michael VasilevskySolutions ArchitectCommented:
fyed is correct. A single quote before the double should work as well:


DoCmd.RunSQL "UPDATE branch SET branch_text = '" & Replace([strHTML], Chr(34), """") & "' WHERE branch_id = " & Form_frmReport.cboChooseBranch.value
0
 
for_yanCommented:


perhaps using Chr(34) could help like here:

MsgBox "testing " & Chr(34) & "123" & Chr(34)

see also here about using Chr(34) annd also escaping double quote
with two double quotes:
http://www.mrexcel.com/forum/showthread.php?p=1561194






0
 
DalexanAuthor Commented:
I love experts exchange, thank you. Such a simple problem that could be solved collectively allowing me to work on other issues without spending time on this.....
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now