asked on

memo field is garbage on Access join/query

Simple query returning data from two tables, using LEFT join (all from table1 and only matching from table2)
When the column on the right side (table2) is a memo field, the query displays 'garbage', not null, not 1st 255 characters, 2 square boxes or sometimes other wierd characters

The join is done on text field, not the memo fields

Is there anyway to do this short of coding?

ASKER CERTIFIED SOLUTION

Arji

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman

allan1956,

Some things to investigate:

Does the memo field's data display properly when the tables are not joined?

The memo field may have some special control, on non-printing characters in it. The join may be exploitiong these characters?

Try copying the memo data from one record and pasting it to notepad.
Strip out anything that is not ASCCI text, then paste it bact to the memo field.
Then try your join.

If you are joining on the text fields, make sure the Fieldsize is the same for both fields. (should not matter, but worth a try)

Make sure the data in the memo field does not exceed 65,000 charaters (memo field limit)

Make sure there is no Format proprty set for this memo field.

Does the memo data display correctly if you chage the join type (Right, Inner)?

How many records are in the table in question.
Because you cannot index a memo field, perhaps with lots of text (ex.: 55,000 chars) and lots of records, the join make the query update too slowly, and does not display the memo data correctly.

Make a copy of the two tables and delete most of the records, then do the join, then inspect the reult.

Hope this helps as well

mbizup

According to microsoft:
>The fields you join on must have similar data types, and you cannot join on MEMO or OLEOBJECT data types.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp