Problem with a ' in a databasefield. (SOLVED)

Hello,

I had a problem and I couldn't figure out what it was.
But after many mindbreaking I found out it was about a ' in a particular field.

I've got a Access database and in there is a table with a field of the Memo type. Now this field for example contyains the following text:

Many COA's have not been booked properly.
Result: A COA without values

Now it has an end of line but that isn't the problem I'm facing it's the '.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Test' Test' Test''.

/Kamsystem/include/connect.asp, line 29
line 29 is the submitting of the Update querry.


I'm doing an UPDATE on a table with the memo field. If that field contains a ' it gives an error, I think it's because all the dfata behind the comma isn't inserted anywhere. So it cancels the update.

Shouldn't it be possble to have ' inserted and updated in a memo field?

I can't figure out how.

Thanks.

Any time you put text data into SQL, replace any single quotes in the data with two single quotes (eg: '').

so to put in this text:
'Test' Test' Test'
you need to do something like this:

Code:

---

Update table Set field = '''Test'' Test'' Test'''

---

Another alternative is using the Replace function to replace all single quotes (') with the apostrophy (`) which works well for storing text. Then you need the same replace on the search field to replace any single quotes before searching the db.

Just another option.

Vince

the trouble with that is that you are changing the data, replacing single quotes with two simply alters the SQL ;)

This works when updating:

Function FormatText (inputText)
Dim textString, descr
dateString = inputText & ""
dateString = replace(dateString, "'", "''")
FormatText=dateString
End Function

Thanks for helping me out.