Solved

Syntax Error: updating memo data type

Posted on 2004-09-29
2
362 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Office 365 home questions 7 65
Search for text in a .txt file 14 45
Create a EXCEL FIle from Access Procedure 1 16
MS Access Delete All Excel sheets 1 9
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

778 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