Solved

Syntax Error: updating memo data type

Posted on 2004-09-29
2
366 Views
Last Modified: 2010-05-18
Table1 has the following columns:

DefectID = (autonumber)
Description = memo


My form has two text boxes one for description one for response. After a response has been entered, the user can click the 'ADD RESPONSE' button to run the query below. However, I am getting syntax errrors.

--------------------------------------------------------------------------------------------------
updateQuery = "UPDATE Table1 SET Description = ' " & descriptionData & _
                  " ' + chr(13) + chr(10) +  chr(13) + chr(10) + ' " & responseData & _
                  " ' WHERE DefectID = " & defectIDValue

 DoCmd.RunSQL updateQuery
--------------------------------------------------------------------------------------------------

I believe my problem stems from having special characters in the Description and/or Response the following is the data giving me problems:

(Description)
------------
Commodity>Commodity MECC200D.
Database error on 'select' statement executed before 'delete' statement.  Cause - unique record key obtained from UI were upended by space in
<input type=hidden…"KEY">
Error was tracked down to AddChgDelTable.toHtml() method, line 521:
(Label) getElement(elementindex)).toString()
Label.toString() is returning ComplexText.toString()

(Response)
------------
Close Question.


Any suggestions? Thanks for a quick response.
0
Comment
Question by:losylos465
2 Comments
 
LVL 1

Accepted Solution

by:
MargusLehiste earned 500 total points
ID: 12182575
Looks like you stated problem best by yourself:
" having special characters in the Description "

If you have  '    character in SQL statement - your program will think you are ending your SQL statement.
Try REPLACE function (Im not sure about syntax in Access but it should be like this:)

updateQuery = "UPDATE Table1 SET Description = ' " & REPLACE(descriptionData, "'", "''") &  "' "  & _
                  " ' WHERE DefectID = " & defectIDValue

You want to replace one ' (single quote)  with two '' (single quotes) -
<to the DB it will be entered as 1 quote>.

0
 

Author Comment

by:losylos465
ID: 12182841
thanks for your help the replace worked!!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

830 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