Did you make the control AND the section able to Can Grow?

Let's see some screen shots.

Yes it is an grow for both.

*Can grow. Working on screenshots, everything is in French so I need to change some stuff for the comprehension...

The language doesn't matter. It could be in Klingon. I just want to see what's happening.

It may be due to the query.  If the query does any "processing" to the long text field, it will truncate it to 255 characters.  For instance, the two queries below will truncate the long text field:

     SELECT DISTINCT LongTextField FROM Table1

     SELECT LongTextField FROM Table1 UNION SELECT LongTextField FROM Table2

because the keywords DISTINCT and UNION do extra processing to the long text field.

The reason is that Access wants to prevent any slow down.  A long text field can store billions of bytes, and Access has to put a limit.

To prevent truncation, make sure no extra processing is done to long text fields.

I do have Select Distinct in my query, how would deal with it considering I have about 20 other fields that is in the Select part

Removing DISTINCT shows a lot of double records. Instead of a 3 pages report, its like 30 pages.

Good catch, Kevin.

Simon, limit your records first with the DISTINCT keyword and then make a 2nd query to bring back in the long text fields after that point.

This has actually been on my list for a video for along time now. Someone asked me this a few months ago. I'll bump it up the list a bit.

Ok thanks guys, Im trying that

Using DISTINCT doesn't necessarily remove duplicates.  DISTINCT only removes duplicates where ALL corresponding fields have the same data in all the records across the board.  E.g.:

Field1   Field2   Field3
one      two      three
one      two      three
one      two      three

But in a normalized database, you shouldn't see this, ever.  This usually indicates something is wrong in the setup.

DISTINCT will not remove duplicates in cases like below:

Field1   Field2   Field3
one      two      three
one      three    four
one      four     five

You will still see duplicates of "one" in this case, because the records themselves are not duplicates of one another.

Can you suggest a video or other links to find what I can do to improve the databse and get rid of the duplicates. Thanks

Start with Normalizing Data and if you want to get all the details, go to Access Relationship Seminar

Hi Simon, normalization means each piece of data should be stored in one place only, hence no duplicates.  I doubt you would type every long text in multiple places in your data entry, so I'm guessing you get those duplicates from queries.  But you have 30 pages of duplicates, 30 pages of data where every corresponding column has identical values in every record.  E.g.:

Field1   Field2   Field3
one      two      three
one      two      three
one      two      three
one      two      three
one      two      three
one      two      three
one      two      three
one      two      three
one      two      three
one      two      three
one      two      three
one      two      three
one      two      three
one      two      three
one      two      three
one      two      three
one      two      three
one      two      three
etc.

If the data look like the above, in a table or query, there is something wrong with the data.

Duplicates that are "normal" would look like the picture below.  This query joins two normalized tables, and result shows some duplicated data.  But each record is *still unique* -- the invoice numbers and dates are different.  If SELECT DISTINCT were used here, it would not affect the result, and those duplicates would not be removed.   In this case the duplicates are "normal" -- that's because if you list all the invoices from the same customer, then naturally the addresses of those customers will be the same in all the records.

If you can show us a sample of what your data looks like, and your resulting query, that would help.

Found the problem, I was storing a CustomerID and was using a query inside my report query to get the CustomerName instead of the autonumber(I did this originally because I had duplicates from my junction table...). I changed the controls on the report to use a Dlookup instead and everything works fine now, no duplicates and all the long text is there. Thank you all for your time