Solved

Syntax Error: updating memo data type

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now