Update query with WHERE-clause containing Long Text field not working

[Derevon]

Hi everyone,

I have a table with a field called Comment of data type "Long Text", and I want to make an update query that adds a certain text, say "abc" + what is already in the field to all lines except those which don't already have this text.

My query looks somthing like:

Update tbl_a SET tbl_a.Comment = "abc" & Chr(13) & Chr(10) & tbl_a.Comment WHERE Not InStr(tbl_a.Comment, "abc");

The above will always update 0 rows no matter what I do with the condition. As a matter of fact, I'm not even able to make a SELECT query using a WHERE-clause with this field. Even after converting it to Short Text. I can only guess that this has to do with the new line characters in the string; it's the only idea I have.

Does anyone know of a way to solve or circumvent this issue?

Thank you very much

 

[The_Doc_Man]

WHERE NOT INSTR(...) is the wrong use of INSTR. INSTR returns a number, not a BOOLEAN. The syntax looks like this:

InStr([start, ]string1, string2[, compare])

See also this reference: https://msdn.microsoft.com/en-us/library/aa445031(v=vs.60).aspx

You might find better results with

WHERE ( INSTR( 1, tbl_a.comment, "abc" ) = 0 ) AND ( tbla.comment <> "" )

This will allow a blank tbl_a.comment to remain blank but if it is not blank, then the INSTR will return either 0 or the position of "abc" in the string. Since INSTR returns are 1-based, if you have already inserted "abc" once, it will be in position 1 (therefore not 0). If "abc" is not in the string and the string is not blank, that record would be selected.

 

[Derevon]

Thank you, but it seems the problem has nothing to do with the condition itself. Even this doesn't work:

SELECT * FROM tbl_a WHERE Comment <> "";

It will return 0 rows, even though there should be thousands.

Perhaps my table is simply corrupt somehow.

 

[Galaxiom]

<>"" does not work for a Null value.

WHERE Comment Is Null

 

[Galaxiom]

Also, Long Text (AKA Memo) don't work with Where clauses in Access.

 

[The_Doc_Man]

Galaxiom - are you sure that Memo/Long Text doesn't work with WHERE? Or is it merely that you cannot index a LongText field type?

Allen Browne has an article that says Memo fields are at least searchable. That implies various string functions should work.

 

[Derevon]

I somehow managed to get it to work in the end. I replaced all null values for the comment field with "" and set "" as its default value, and then used a where clause something like: WHERE Comment Not Like "*abc*". Thanks for all help.

 

[Galaxiom]

Galaxiom - are you sure that Memo/Long Text doesn't work with WHERE? Or is it merely that you cannot index a LongText field type?

I avoid using them so I am not terribly familiar. I just had a quick look and they can be searched with criteria.

The issue is Grouping where they behave like they only have the first 255 characters. There are stories of losing the data after character 255 during updates. Then there was the corruption issue where they turn into Chinese characters.

I would not be surprised if they had other idiosyncrasies lurking.

When I have encountered developers appending to memo fields to keep a running commentary, I usually suggest they use a related table with separate comments that fit in a Short Text field.

That structure is certainly faster and more reliable.

 

[The_Doc_Man]

No dispute there, my friend. Various discussions on-line (not in forums) suggest that there are many functions that won't operation correctly if the input is Long Text or Memo. The truncation at 255 characters is common, but some other oddball truncation events have also been reported for larger limits. I kind of like the "Chinese character" event. It is just whacky enough to appeal to my warped sense of humor. But of course it is not so great if you are dealing with a production database that drops into Mandarin now and then.