[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


memo field is garbage on Access join/query

Posted on 2006-05-31
Medium Priority
Last Modified: 2012-06-21
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?  
Question by:allan1956
LVL 17

Accepted Solution

Arji earned 400 total points
ID: 16887034
It sounds to me there is some sort of mismatch in the Join fields.  Although it may work sometimes, it's always best practice to use a Primary Key/Foreign Key relationship to Join two tables or at least something numeric.  As you may know, usually those fields are large integer fields which are processed much more accurately and quickly than text.  
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 16998380

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
LVL 61

Expert Comment

ID: 17142921
According to microsoft:
>The fields you join on must have similar data types, and you cannot join on MEMO or OLEOBJECT data types.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question