Dealing with Memo fields in a query!?!?!?

[LJtechnical]

HI
I am trying to create a simple query that uses a link between two tables fields which are both Memo fields, but I get the "Can't join on MEMO or OLE object" error. Is there a way around this at all?

Cheers

 

[Norris68]

No. A memo field is stored differently to text, and is regarded as a BLOB (or binary large object) by Access. Memo fields are not stored directly in the table as other field types are, but internally a reference is stored pointing to the location within the internal blob table. This is why you cannot join on a memo field, as the processing overhead is just too great.

The only way round it would be to either summarise the memo into a normal text field, or store your comments differently as in a subtable of text fields (like a blog) so that no individual entry exceeds the maximum size for a text field.

 

[LJtechnical]

So if i am confident none of the data for the memo fields are over 255 chars then is it possible to automate copying the whole field in to a another field or table as text?

 

[Norris68]

UPDATE MyTable SET MyTextField = Left$(MyMemoField,255)
- this will truncate the memo field if it is too big.

To find out if any need truncating, run a quick query ...
SELECT MyMemoField FROM MyTable WHERE Len(MyMemoField) > 255