memo field is garbage on Access join/query

Posted on 2006-05-31
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

    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

    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now