Sql syntax error

[Jen0dorf]

Hi

single table containing two fields notes and notes_2

I've (with help) merged and deleted most records and am left with 4332 records to update

these 4332 records: both notes and notes_2 have entries that are not identical but the field in notes_2 is the upto date one.

So I want to update the notes field with the one from notes_2 without effecting the other 39000 records in the table

So I created a query that save with no error

UPDATE tblDeceased SET tblDeceased.Notes = [tblDeceased]![notes_2]
WHERE (((tblDeceased.notes_2)="IsNotNull" And [tblDeceased]![Notes]<>[tblDeceased]![notes_2]));

I think my logic says update Notes with the value from Notes_2 providing notes_2 contains data and notes and notes_2 are not equal



I run the query and it tells me 0 records are to be updated yet if I visibly look I can see the records I want to update.

I've been caught out before with wording of queries (And caused me hours of stress) , is my code wrong?

thanks

Ian

[ranman256]

Is NOTES field is a MEMO type?
you can't query memo fields.

[Jen0dorf]

Hi

both fields are long text, I've been experimenting and I suspect my sql is flawed with IsNotNull?

If I create a test query and run it with ISNOTNULL as criteria is still lists fields that I consider visually to be empty

SELECT tblDeceased.Notes, tblDeceased.notes_2
FROM tblDeceased
WHERE (((tblDeceased.notes_2)="Is Not Null"));

shows 0 entries

SELECT tblDeceased.Notes, tblDeceased.notes_2
FROM tblDeceased
WHERE (((tblDeceased.notes_2)=Is Not Null));

Shows all records regardless of if Notes_2 field is empty or not

So does Is NOT NULL not mean contains data of some sort?

thanks

Ian

[cyanidem]

Try:
WHERE ((Not (tblDeceased.notes_2) Is Null));

[orange]

A couple of points:

---
"Is Not Null" -- in quotes would be a literal string.
Is Not NULL -- is an SQL expression. It is not used with "="

Reference to SQL and examples

----

Is NOTES field is a MEMO type?
you can't query memo fields.

This statement is not true. Perhaps ranman256 meant something else.

Here is an example of querying a memo field.

The TXT field of table tbl_Spl_LCL_Std_EstProfileEng is Memo data type. The length of the TXT value is 1595.
The query selects the last 20 characters of TXT for ESTBLMT_NO 123456197946 but also shows the entire TXT field.

The table structure is:

    table_name                               field_name        field_description        data_type    
    tbl_Spl_LCL_Std_EstProfileEng        RecID             Record Identifier               Long        
    tbl_Spl_LCL_Std_EstProfileEng        Est_Name       Establishment Name          Text        
    tbl_Spl_LCL_Std_EstProfileEng        ESTBLMT_NO  Establishment Number       Text        
    tbl_Spl_LCL_Std_EstProfileEng        TXT_TYP_CD   Text Type Identifier            Text        
    tbl_Spl_LCL_Std_EstProfileEng        TXT                Description text  (memo)    Memo

Here is the query sql:

SELECT tbl_Spl_LCL_Std_EstProfileEng.ESTBLMT_NO
, Len([TXT]) AS LENGTH
, Right([txt],20) AS Last20
, tbl_Spl_LCL_Std_EstProfileEng.txt
FROM tbl_Spl_LCL_Std_EstProfileEng
WHERE
 (((tbl_Spl_LCL_Std_EstProfileEng.ESTBLMT_NO)="123456197946"));

Attached is a picture(jpg) of the output of the query.