Updating a memo field with a "Select" statement

[Guest]

Hello, I am trying to update a memo field with a SQL query dynamically. This
query is a PL/SQL query whose syntax will be alien to Jet engine. I am not
able to do that as MS access gives syntax error as it reads the text
containing query as executable statment and not as a text.
Can someone help on what expression I should be using before updating the
memo field with a SQL query statement?

 

[Guest]

My statement below was not accurate. The actual problem I face is that, the
long string which I am trying to update in the memo field contains single
quotes. ( " ).
As the usual rule for a text field, I prefix the data with double quotes in
the update query. When I try to execute the query it shows me error at the
place where it finds a double quotes within the text.

In the below query, it gives problem at the place where i specify
"Resource". We can't change the data because the query I am trying to update
in the memo field is in Oracle syntax. I have an application that will
retrieve the query from this access db and run in a oracle db server.

UPDATE Table1 SET b = "SELECT E.RESOURCE_ID
"Resource",SUBSTR(E.EMP_NAME,1,30) "Name", E.GROUP_TYPE "Type",
X.PARENT_GEOG_CODE "Patch", to_char((H.start_date),'WW')" WHERE ID=1;

 

[John Spencer]

Try doubling the quotes to end up with a string that has single quotes

UPDATE Table1 SET b =

"SELECT E.RESOURCE_ID ""Resource"",
SUBSTR(E.EMP_NAME,1,30) ""Name"",
E.GROUP_TYPE ""Type"",
X.PARENT_GEOG_CODE ""Patch""
, to_char((H.start_date),'WW')"

WHERE ID=1;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..