Compare two Memo fields

[JW61]

I receive information from another system via a delimited text file which is imported into a table. The field ‘Desc’ was always such that I could use a text field. The information has now grown to that I have changed to memo field. Currently the information can be up to 1100 characters.

I would compare the previous file to the current import so that I could identify any changes that were made. The file contains thousand of records so I need a way to identify only the ones changed.

Below is the original SQL I used to compared the ‘Desc’ field to find any changes using a subquery.

SELECT DISTINCT tblNotesCopy.CustNum, tblNotesCopy.OrdNum, tblNotesCopy.Desc, tblNotes.Desc INTO tblOrderNotesChanges
FROM tblNotes INNER JOIN tblNotesCopy ON (tblNotes.SeqNum = tblNotesCopy.SeqNum) AND (tblNotes.RecType = tblNotesCopy.RecType) AND (tblNotes.CustNum = tblNotesCopy.CustNum) AND (tblNotes.OrdNum = tblNotesCopy.OrdNum)
WHERE (((tblNotesCopy.OrdNum) In (Select OrdNum from tblNotes)) AND ((tblNotesCopy.Desc) Not In (Select Desc from tblNotes)));

It will not let me use the memo field as part of a subquery.

How can I compare two memo fields to determine if they are not equal i.e. changes have been made?

 

[BigRed1212]

Is anything on this page helpful?

http://allenbrowne.com/ser-63.html

 

[SeeThru]

You could use the len(field) and see if the length has changed. Not 100% reliable, but something.

If the option is there, see if the other system can output a last update timestamp.

Or, if the older information is always output, and you do not change it in your database, consider allways updating every record, so you will get all changes.

SeeThru
Synergy Connections Ltd - Telemarketing Services

http://www.synergyconnections.co.uk