Solved

Syntax Error: updating memo data type

Posted on 2004-09-29
2
368 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

718 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