Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Syntax Error: updating memo data type

Posted on 2004-09-29
2
Medium Priority
?
373 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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

604 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